在 VBA 自动过滤器中使用字符串数组作为条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38404176/
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
Using string array as criteria in VBA autofilter
提问by slzar
I've searched other posts and found similar issues but nothing that could help me specifically. I'm trying to take an array of strings and use it as a filter criteria. It's tricky because the array is created by a function and has a variable number of elements and contents. I need the autofilter to take it and check column Efor each one of its elements.
我搜索了其他帖子并发现了类似的问题，但没有什么可以特别帮助我。我正在尝试获取一组字符串并将其用作过滤条件。这很棘手，因为数组是由函数创建的，并且具有可变数量的元素和内容。我需要自动过滤器来获取它并检查E 列的每个元素。
I've tried it two ways
我试过两种方式
1)
1)
With Sheet17
.Range("E1").AutoFilter Field:=5, Criteria1:=Application.Transpose(arr)
End With
Result: Applies a filter to column E but fails to select any of the options
结果：对 E 列应用过滤器但未能选择任何选项
2)
2)
For i = 0 To counter - 1
With Sheet17
.Range("E1").AutoFilter Field:=5, Criteria1:=Application.Transpose(arr(i))
End With
Next
Note:Counter is an integer representing the number of elements in the array.
注意：Counter 是一个整数，表示数组中元素的数量。
Result:This one correctly loops through the array but only selects the last option on the filter - presumably because every time it loops back through it starts over and unchecks every other option so by the end only the most recent option remains checked.
结果：这个正确地循环遍历数组但只选择过滤器上的最后一个选项 - 大概是因为每次循环返回它都会重新开始并取消选中所有其他选项，因此到最后只有最近的选项保持选中状态。
回答by
You do not need to transpose a single element from an array and you cannot put criteria into the 5thfield if you are only referencing column E.
您不需要转置数组中的单个元素，并且如果您仅引用列 E，则无法将条件放入第 5个字段。
Dim i As Long, arr As Variant
arr = Array(1, 3)
With Sheet17
'to filter each value in the array one at a time
For i = 0 To UBound(arr)
.Columns("E").AutoFilter Field:=1, Criteria1:=arr(i)
Next i
'my values were numbers - AutoFilter likes strings in its array
For i = LBound(arr) To UBound(arr)
arr(i) = CStr(arr(i))
Next i
'to filter all values in the array at once specify xlFilterValues
.Columns("E").AutoFilter Field:=1, Criteria1:=arr, _
Operator:=xlFilterValues
End With
Specify the Operator:=xlFilterValueswhen passing an array and the Range.AutoFilter Methodlikes strings as the values in an array.
指定Operator:=xlFilterValues传递数组和Range.AutoFilter 方法时将字符串作为数组中的值。
回答by Brett Wolfington
The Excel documentation for AutoFilterprovides some guidance. The Operatorparameter takes a XlAutoFilterOperatorthat specifies how Criteria1is interpreted. In your case, specifying a value of xlFilterValueswill cause Criteria1to be properly interpreted as an array of filter values.
AutoFilter的 Excel 文档提供了一些指导。该Operator参数采用XlAutoFilterOperator指定如何Criteria1解释。在您的情况下，指定值xlFilterValues将导致Criteria1被正确解释为过滤器值数组。
The following example demonstrates this:
以下示例演示了这一点：
Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie")
Sheet17.Range("E1").AutoFilter _
Field:=5, _
Criteria1:=arr, _
Operator:=xlFilterValues
