vba 使用数组和 xlFilterValues 过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42932353/
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
Filter using array and xlFilterValues
提问by user1778266
I have written a code which defines an array and then uses that array as criteria to filter a range. Here's the extract of the code. Somehow it filters out everything and does not display the filtered values.
我编写了一个代码，它定义了一个数组，然后使用该数组作为过滤范围的条件。这是代码的摘录。它以某种方式过滤掉所有内容，并且不显示过滤后的值。
Dim N As Long
Sheets("Calculations").Select
With Sheets("Calculations")
N = .Cells(Rows.Count, "A").End(xlUp).Row
ReDim ary(1 To N)
For i = 1 To N
ary(i) = .Cells(i, 1)
Next i
End With
Sheets("Data").Select
Range(Range("A1"), Range("A1").End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.AutoFilter Field:=36, Criteria1:=ary, Operator:=xlFilterValues
End With
回答by
Are the values in column A numbers? When using a variant array as the Criteria1 with xlFilterValues, numbers must be treated as text so use ary(i) = CStr(.Cells(i, 1).Value2)to build your array.
A 列中的值是数字吗？当使用变体数组作为带有 xlFilterValues 的 Criteria1 时，数字必须被视为文本，以便用于ary(i) = CStr(.Cells(i, 1).Value2)构建数组。
Dim ary As Variant
With Worksheets("sheet1").Cells(1, 1).CurrentRegion
'with true numbers in column A this DOES NOT work
ary = Array(1, 2, 3)
.AutoFilter field:=1, Criteria1:=ary, Operator:=xlFilterValues
'with true numbers in column A this DOES work
ary = Array("1", "2", "3")
.AutoFilter field:=1, Criteria1:=ary, Operator:=xlFilterValues
End With
Yes, this seems counter-intuative but that is how to filter for numbers using an array with xlFilterValues. Dates can present a similar issue.
是的，这似乎违反直觉，但这就是使用带有 xlFilterValues 的数组过滤数字的方法。日期可能会出现类似的问题。
回答by BRCoder
When filtering by an array you must use the transpose method:
按数组过滤时，您必须使用转置方法：
after Criteria1:=your code should read Application.Transpose(ary)
在Criteria1:=您的代码应该阅读之后Application.Transpose(ary)
