VBA 多标准/变量过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19224305/
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
VBA Multiple Criteria / Variable Filter
提问by user2298601
I need some help regarding filtering in excel, and would greatly appreciate any assistance. I would like to be able to filter by variable criteria, and possibly by multiple variable criteria. Allow me to explain this as simple as I possibly can
我需要一些有关 excel 过滤的帮助，非常感谢任何帮助。我希望能够按可变标准进行过滤，也可能按多个可变标准进行过滤。请允许我尽可能简单地解释一下
I have a workbook, in Sheet 2 I have data. In Sheet 1, I have a list of variables which I need to be filtered in sheet 2. These variables will vary in quantity, and of course the data in each cell will vary.
我有一本工作簿，在 Sheet 2 中有数据。在工作表 1 中，我有一个需要在工作表 2 中过滤的变量列表。这些变量的数量会有所不同，当然每个单元格中的数据也会有所不同。
Now here is where I have my humble request(s).
现在这是我提出谦卑请求的地方。
Can I possibly have code for two separate functions:
我可能有两个独立函数的代码：
1) For excel to register how many variables there are, and to filter each of these variables one by one (I have code that will do what I require and then reset the filter).
1）让excel注册有多少变量，并一个一个过滤这些变量中的每一个（我有代码可以做我需要的然后重置过滤器）。
2) For excel to register the variables and filter all of them at the same time (multiple criteria).
2）让excel注册变量并同时过滤所有变量（多个条件）。
I have attached a link to an example excel spreadsheet. I hope it helps!
我附上了一个示例 Excel 电子表格的链接。我希望它有帮助！
http://www.filedropper.com/excelexample
http://www.filedropper.com/excelexample
I really appreciate any help on this.
我真的很感激这方面的任何帮助。
Thank You
谢谢你
采纳答案by Gary's Student
In this small example, we gather the values from the Info tab, remove any blanks and apply that filter to the first column of the Data tab so all matches will be displayed:
在这个小示例中，我们从“信息”选项卡收集值，删除所有空白并将该过滤器应用于“数据”选项卡的第一列，以便显示所有匹配项：
Sub luxation()
Dim sh1 As Worksheet, N As Long
Dim st As String
Set sh1 = Sheets("Info")
N = sh1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To N
v = sh1.Cells(i, 1).Value
If v <> "" Then
st = st & v & ","
End If
Next i
st = Mid(st, 1, Len(st) - 1)
Arr1 = Split(st, ",")
Sheets("Data").AutoFilterMode = False
With Sheets("Data").Range("$A:$C44")
.AutoFilter Field:=1, Criteria1:=Arr1, Operator:=xlFilterValues
End With
End Sub
In the next example, the filter values are applied sequentially:
在下一个示例中，过滤器值按顺序应用：
Sub luxation2()
Dim sh1 As Worksheet, N As Long
Dim st As String
Set sh1 = Sheets("Info")
N = sh1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To N
v = sh1.Cells(i, 1).Value
If v <> "" Then
st = st & v & ","
End If
Next i
st = Mid(st, 1, Len(st) - 1)
Arr1 = Split(st, ",")
Sheets("Data").Activate
For i = LBound(Arr1) To UBound(Arr1)
Sheets("Data").AutoFilterMode = False
With Sheets("Data").Range("$A:$C44")
.AutoFilter Field:=1, Criteria1:=Arr1(i), Operator:=xlFilterValues
End With
MsgBox "Check out the filter"
Next i
End Sub
