过滤不同列中的多个条件，包括 VBA 中的字符串和日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41338398/
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
Filtering for multiple criteria in different columns including string and date in VBA
提问by TarunS
I have two columns namely "Status" and "Finish Date". I want to filter for "OPEN" and "CLOSED" in Statuscolumn first and then filter for "1/0/1900" in Finish Datecolumn.
我有两列，即“状态”和“完成日期”。我想先在“状态”列中过滤“OPEN”和“CLOSED” ，然后在“完成日期”列中过滤“1/0/1900” 。
For this I wrote the following code:
为此，我编写了以下代码：
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
.AutoFilterMode = False
.Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:=Array( _
"OPEN", "CLOSED"), Operator:=xlFilterValues
.Range("A1:A" & LR).AutoFilter Field:=2, Criteria1:=Array(0, "1/0/1900"), Operator:= _
xlFilterValues
End With
But this did not work. At first I get the Error (AutoFilter Method of Range Class Failed 1004). Then by mistake I removed the 1st line in the With statement(.AutoFilterMode = False) and I did not get any error. However the filter returned no results at all.
但这不起作用。起初我收到错误（范围类的自动过滤方法失败 1004）。然后我错误地删除了 With 语句中的第一行 (.AutoFilterMode = False) 并且我没有收到任何错误。然而，过滤器根本没有返回任何结果。
I thought that there is something wrong with my date filter since the Status column filter was working earlier. So I researched and wrote the code below for the date filter alone but again it does not work.
我认为我的日期过滤器有问题，因为状态列过滤器工作较早。所以我研究并单独为日期过滤器编写了下面的代码，但它再次不起作用。
Dim dDate As Date
Dim strDate As String
Dim lDate As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
dDate = DateSerial(1900, 1, 0)
lDate = dDate
ActiveSheet.Range("A1:A" & LR).AutoFilter Field:=2, Criteria1:=">=" & lDate, _
Operator:=xlAnd, Criteria2:="<" & lDate + 1
Please help in getting these two filters to work.
请帮助使这两个过滤器工作。
Thanks in advance.
提前致谢。
回答by user3598756
you mess things up a little bit
你把事情搞砸了一点
try this
尝试这个
Sub Test()
With ActiveSheet '<--| reference your sheet
.AutoFilterMode = False '<--| remove any existing filtering
With Range("B1", .Cells(.Rows.count, 1).End(xlUp)) '<--| reference its range in columns A:B from row 1 down to column A last not empty row
.AutoFilter Field:=1, Criteria1:=Array("OPEN", "CLOSED"), Operator:=xlFilterValues '<--| filter on referenced range 1st column with "OPEN" or "CLOSED" values
.AutoFilter Field:=2, Criteria1:="1/0/1900" '<--| filter on referenced range 2nd column with "1/0/1900" value
End With
End With
End Sub
BTW you'd better substitute ActiveSheetwith some Worksheets("mySheetName")(where you change "mySheetName" to your actual sheet name) since relying on ActiveSheetcan be misleading
顺便说一句，你最好ActiveSheet用一些替代Worksheets("mySheetName")（你将“mySheetName”更改为你的实际工作表名称），因为依赖ActiveSheet可能会产生误导
回答by user3391546
TarunS, looks like you are selecting only one column ( Column A ) in the Range() but trying to apply filter in two columns. I have created excel worksheet where column A is "Status" and column B is "Finish Date" and I am able to create filter as you explained with following code:-
TarunS，看起来您在 Range() 中只选择了一列（ Column A ），但试图在两列中应用过滤器。我创建了 excel 工作表，其中 A 列是“状态”，B 列是“完成日期”，我可以按照您使用以下代码的解释来创建过滤器：-
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
.AutoFilterMode = False
.Range("A1:B" & LR).AutoFilter Field:=1, Criteria1:="=CLOSED", _
Operator:=xlOr, Criteria2:="=OPEN"
.Range("A1:B" & LR).AutoFilter Field:=2, Criteria1:="1/0/1900"
End With
Please note that I have equal number of items in Column A and Column B and I am using Excel 2010. Let me know if you still face any problem with the code.
请注意，我在 A 列和 B 列中有相同数量的项目，并且我使用的是 Excel 2010。如果您仍然遇到代码问题，请告诉我。
回答by Swapnil
I Consider your data is like as below
我认为您的数据如下
column A Column B
Status Finish Date
OPEN 1/0/1900
CLOSED 1/0/1900
OPEN 1/0/1900
CLOSED 1/0/1900
OPEN 1/0/1904
CLOSED 1/0/1905
OPEN 1/0/1906
CLOSED 1/0/1907
OPEN 1/0/1908
CLOSED 1/0/1909
OPEN 1/0/1910
CLOSED 1/0/1911
OPEN 1/0/1900
CLOSED 1/0/1900
OPEN 1/0/1900
CLOSED 1/0/1900
OPEN 1/0/1900
CLOSED 1/0/1900
OPEN 1/0/1905
OPEN 1/0/1906
CLOSED 1/0/1906
OPEN 1/0/1906
CLOSED 1/0/1906
OPEN 1/0/1906
CLOSED 1/0/1906
OPEN 1/0/1906
CLOSED 1/0/1906
OPEN 1/0/1906
x 1/0/1906
y 1/0/1906
Z 1/0/1906
A 1/0/1906
b 1/0/1906
You can Type the formula in column C (at C2 Cell)
您可以在 C 列中键入公式（在 C2 单元格中）
=IF(AND((OR(A2="open",A2="Closed")),B2= "1/0/1900"),"Yes","No")
=IF(AND((OR(A2="open",A2="Closed")),B2="1/0/1900"),"是","否")
Drag this formula till last row(last of column A or B)
将此公式拖到最后一行（A 列或 B 列的最后一行）
It will gives you output like below:
它将为您提供如下输出：
Status Finish Date Result
OPEN 1/0/1900 Yes
CLOSED 1/0/1900 Yes
OPEN 1/0/1900 Yes
CLOSED 1/0/1900 Yes
OPEN 1/0/1904 No
CLOSED 1/0/1905 No
OPEN 1/0/1906 No
CLOSED 1/0/1907 No
OPEN 1/0/1908 No
CLOSED 1/0/1909 No
OPEN 1/0/1910 No
CLOSED 1/0/1911 No
OPEN 1/0/1900 Yes
CLOSED 1/0/1900 Yes
OPEN 1/0/1900 Yes
CLOSED 1/0/1900 Yes
OPEN 1/0/1900 Yes
CLOSED 1/0/1900 Yes
OPEN 1/0/1905 No
OPEN 1/0/1906 No
CLOSED 1/0/1906 No
OPEN 1/0/1906 No
CLOSED 1/0/1906 No
OPEN 1/0/1906 No
CLOSED 1/0/1906 No
OPEN 1/0/1906 No
CLOSED 1/0/1906 No
OPEN 1/0/1906 No
x 1/0/1906 No
y 1/0/1906 No
Z 1/0/1906 No
A 1/0/1906 No
b 1/0/1906 No
then you can easily set the filter on column c as criteria "Yes" or if you want to copy the data , set the filter on "yes"
那么您可以轻松地将 c 列上的过滤器设置为条件“是”，或者如果您想复制数据，请将过滤器设置为“是”
and use the code to copy data
并使用代码复制数据
Range("A2:C1048576").specialcells(xlcellTypeVisible).copy Destination:=Range("D2")
Range("A2:C1048576").specialcells(xlcellTypeVisible).copy Destination:=Range("D2")
