VBA 中的自动筛选条件为一系列单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24679674/
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
Autofilter in VBA with criteria as a range of cells
提问by Vamsi
I want to use autofilter in vba to filter using a dynamic range of cell values.
我想在 vba 中使用 autofilter 来过滤使用动态范围的单元格值。
ActiveSheet.Range("$A:$I4092").AutoFilter Field:=1, Criteria1:=???? _
Operator:=xlFilterValues
I want to use a dynamic range of cells like
我想使用动态范围的单元格，例如
Range("A1",Range("A1").End(xlDown))
Can you suggest how to specify this?
你能建议如何指定这个吗？
I have tried passing the following to Criteria1:
我尝试将以下内容传递给 Criteria1：
Range(###).Value
Array(Range(###))
etc.
等等。
Ex.
前任。
Col1 Col2 Col3 Col4
----------------------------
A 1 3 Y
B 3 3 N
A 2 2 N
C 6 1 Y
B 9 3 Y
I want to filter out the rows with values A& Cin Col1.
我想过滤掉带有值A& Cin的行Col1。
回答by Gary's Student
If we use the Recorder on a small AutoFilterwe see:
如果我们在小型AutoFilter上使用 Recorder，我们会看到：
Sub Macro1()
Range("A1:C20").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$C").AutoFilter Field:=2, Criteria1:=Array( _
"Alice", "Boris", "Mike"), Operator:=xlFilterValues
End Sub
So AutoFilterwants a 1-based array for Criteria1
所以AutoFilter 需要一个基于 1 的数组作为Criteria1
Say the desired list is in sheet xxin column A.We will make an array from this list:
假设所需的列表在A列的第xx表中。我们将从这个列表中创建一个数组：
Sub Macro11()
Dim N As Long, r As Range
With Sheets("xx")
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
Range("A1:C20").AutoFilter
ActiveSheet.Range("$A:$C").AutoFilter Field:=2, Criteria1:=ary, Operator:=xlFilterValues
End Sub
回答by Vamsi
As @xificurC mentioned, Advanced filter does the trick! I was able to use the following code to filter multiple values at once using just a reference to a range
正如@xificurC 提到的，高级过滤器可以解决问题！我能够使用以下代码一次过滤多个值，仅使用对范围的引用
Range("A1:A6").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("A10:A12"), Unique:=False
回答by sous2817
One way would be to define the last row using whatever method you prefer, and then using that value in your function. Something like:
一种方法是使用您喜欢的任何方法定义最后一行，然后在您的函数中使用该值。就像是：
Dim lr As Long
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.Range("$A:$I$" & lr).AutoFilter Field:=1, Criteria1:="Foo"
I would strongly advise you to fully qualify your range references to avoid potential headaches down the road, but this should get you started.
我强烈建议你完全限定你的范围参考以避免潜在的麻烦，但这应该让你开始。
