AutoFilter - 使用 VBA 动态更改过滤条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18151299/
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 - Dydamically change in Filter Criteria using VBA
提问by user2300403
I'm having the same kind of question as in this link - Get AutoFilter sort criteria and apply on second sheet
我遇到了与此链接相同的问题 - 获取自动筛选排序条件并在第二张纸上应用
I've gone thru the link but not able to get the required output.
我已经通过链接但无法获得所需的输出。
I've the filtered criteria in Sheet1 (which we can change as required) on one of the column values (eg: col 10) and now based on what ever the data in column 10 which are shown based on the filter criteria, I want to filter on sheet2 with the data in sheet 1.
我在 Sheet1 中的过滤条件（我们可以根据需要更改）上的列值之一（例如：col 10）现在基于第 10 列中基于过滤条件显示的数据，我想要使用工作表 1 中的数据在工作表 2 上进行过滤。
I have seen that many of them using with static values in ARRAY as shown but how can I autofilter dynamically changing values in the sheet1 and filtering in Sheet2. Please advise
我已经看到他们中的许多人在 ARRAY 中使用静态值，如图所示，但是如何自动过滤 sheet1 中动态更改的值并在 Sheet2 中过滤。请指教
.AutoFilter Field:=10, Criteria1:=Array("value1", "value2"), Operator:=xlFilterValues
.AutoFilter Field:=10, Criteria1:=Array("value1", "value2"), Operator:=xlFilterValues
回答by tigeravatar
I think you want something like this:
我想你想要这样的东西：
Sub tgr()
Dim wsData As Worksheet
Dim wsCriteria As Worksheet
Dim arrCriteria As Variant
Set wsData = Sheets("Sheet2")
Set wsCriteria = Sheets("Sheet1")
arrCriteria = Application.Transpose(wsCriteria.Range("J4", wsCriteria.Range("J4").End(xlDown)).Value)
With wsData.UsedRange
.AutoFilter 10, arrCriteria, xlFilterValues
End With
Set wsData = Nothing
Set wsCriteria = Nothing
If IsArray(arrCriteria) Then Erase arrCriteria
End Sub
回答by Joe Laviano
What if you just define the array in VBA?
如果你只是在 VBA 中定义数组怎么办？
Dim CritArray(2) as String
CritArray(0) = Cells(1,1).Value
CritArray(1) = Cells(2,1).Value
Then just editing your line of code:
然后只需编辑您的代码行：
.AutoFilter Field:=10, Criteria1:=Array(CritArray(0),CritArray(1)), Operator:=xlFilterValues
I dont know how many criteria you have (or their location), but you can add/edit as such. I based this off the fact you only have 2 criteria, but it can be enlarged of course.
我不知道你有多少标准（或他们的位置），但你可以添加/编辑。我基于您只有 2 个标准的事实，但它当然可以扩大。
