在 Excel VBA 中使用多列自动过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2468666/
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 with multi-columns in Excel VBA
提问by Phu Nguyen
I need to use VBA to filter some information in excel. As I have an excel with 20 columns, now want to use AutoFilter function to search in some columns if it contains one value (Ex: ID010). what i want is it'll display all rows that have at least one column contains ID010.
我需要使用VBA来过滤excel中的一些信息。由于我有一个包含 20 列的 excel，如果它包含一个值（例如：ID010），现在想使用自动筛选功能在某些列中进行搜索。我想要的是它会显示至少有一列包含 ID010 的所有行。
Currently, i use the below code to search. However, it could not find any data because all the criteria seem to tie together using AND operator
目前，我使用以下代码进行搜索。但是，它找不到任何数据，因为所有条件似乎都使用 AND 运算符联系在一起
' Search range, [argIn]---> search value
With [D5:M65536]
.AutoFilter Field:=4, Criteria1:=argIn
.AutoFilter Field:=5, Criteria1:=argIn
.AutoFilter Field:=6, Criteria1:=argIn
.AutoFilter Field:=7, Criteria1:=argIn
.AutoFilter Field:=8, Criteria1:=argIn
.AutoFilter Field:=9, Criteria1:=argIn
.AutoFilter Field:=10, Criteria1:=argIn
.AutoFilter Field:=11, Criteria1:=argIn
.AutoFilter Field:=12, Criteria1:=argIn
.AutoFilter Field:=13, Criteria1:=argIn
End With
I wonder if anyone could give me some hints or examples how to handle this issue.
我想知道是否有人可以给我一些提示或示例来处理这个问题。
Thank you in advance.
先感谢您。
采纳答案by Steve Homer
The autofilter functionality won't help you here, as you've discovered the behaviour is to AND the filters together.
自动过滤器功能在这里对您没有帮助，因为您发现行为是将过滤器与过滤器结合在一起。
One approach would be to have a controlcolumn in the spreadsheet (perhaps maintained through VBA) populated with worksheet functions like
一种方法是在电子表格（可能通过 VBA 维护）中使用工作表函数填充一个控制列
=COUNTIF(A:A, "ID010")
And then apply the filter to the control column.
然后将过滤器应用于控制列。
