Excel VBA 如何根据不同工作表中单元格的值删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14017444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA How to delete rows based on the value of a cell in a different worksheet
提问by BigUglyDataScientist
I am trying to create a macro that will automatically delete rows from a raw data file based on multiple criteria. I have been able to create a macro that will delete rows where certain columns have blank values, but I also need to delete rows that have a date that is equal or greater than a date contained in a specific cell in another worksheet. I have been all over the internet but have not found an explanation that addresses this particular case or one similar enough for me to figure it out. I am working on automating a number of other reports, and this is the final piece that I need to be able to finish several of them. Here is the code I am trying to use.
我正在尝试创建一个宏，该宏将根据多个条件自动从原始数据文件中删除行。我已经能够创建一个宏来删除某些列具有空白值的行，但我还需要删除日期等于或大于另一个工作表中特定单元格中包含的日期的行。我一直在互联网上，但还没有找到解决这个特殊情况的解释或一个足以让我弄清楚的解释。我正在自动化一些其他报告，这是我需要能够完成其中几个报告的最后一部分。这是我尝试使用的代码。
Sub Deletedatesbeyondreportmonth()
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
.Columns("E").AutoFilter Field:=1, Criteria1:=">= sheets("Sheet2").Range("C1")"
.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub
If I replace the criteria sheets("Sheet2").Range("C1")with the actual value that is contained in the specified cell the macro works, however I need it to be able to run based off the value contained in that cell. I have played with some syntax variations and got different error messages, but haven't been able to figure it out. If I run this as is, it highlights the word Sheet2 on the criteria line and says:
如果我用sheets("Sheet2").Range("C1")指定单元格中包含的实际值替换条件，宏就可以工作，但是我需要它能够根据该单元格中包含的值运行。我尝试了一些语法变体并收到了不同的错误消息，但一直无法弄清楚。如果我按原样运行它，它会突出显示条件行上的 Sheet2 一词并说：
Compile Error expected: End of statement
预期编译错误：语句结束
回答by Rob I
回答by InContext
when you encapsulate VBA logic by quotes you actually hardcode the string, so it will use the text Sheets("Sheet2").Range("C1")in the filter rather than evaluate the Range value. You need to use the following:
当您通过引号封装 VBA 逻辑时，您实际上对字符串进行了硬编码，因此它将使用Sheets("Sheet2").Range("C1")过滤器中的文本而不是评估 Range 值。您需要使用以下内容：
.Columns("E").AutoFilter Field:=1, Criteria1:=">=" & Sheets("Sheet2").Range("C1")
