用于过滤列的 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13934821/
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
VBA for filtering columns
提问by Ampi Severe
I have a big database-like sheet, first row contains headers. I would like a subset of rows of this table based on column values. Two issues:
我有一个类似数据库的大表，第一行包含标题。我想要基于列值的该表行的子集。两个问题：
1) VBA-wise I would like to loop through the columns, when the values for all necessary columns all match, copy the entire row into a new sheet.
1）VBA-wise 我想遍历列，当所有必要列的值都匹配时，将整行复制到新工作表中。
2) The subset of rows is based on a list. I just read I can use Autofilter with an array. Is it possible to input this array from a column instead of manually entering it in the VBA code? The list I'm using consists of 200 different strings and will be updated periodically.
2) 行的子集基于列表。我刚刚读到我可以将 Autofilter 与数组一起使用。是否可以从列中输入此数组而不是在 VBA 代码中手动输入？我使用的列表由 200 个不同的字符串组成，并且会定期更新。
Where CritList is the list of strings. I still need to figure out how, but now I leave the office, so more tomorrow.
其中 CritList 是字符串列表。我仍然需要弄清楚怎么做，但现在我离开了办公室，所以明天更多。
EDIT1Thanks to @DougGlancy; the autofiltering works now. Here is his beautiful code (I only added the array-filter).
EDIT1感谢@DougGlancy；自动过滤现在有效。这是他漂亮的代码（我只添加了数组过滤器）。
EDIT2Included a more elaborate array-filter, where NameListis the list I would like to filter for. Now it all works!
EDIT2包括一个更精细的数组过滤器，其中NameList是我想要过滤的列表。现在一切正常！
Sub FilterAndCopy()
Dim LastRow As Long
Dim vName As Variant
Dim rngName As Range
Set rngName = Sheets("Sheet3").Range("NameList")
vName = rngName.Value
Sheets("Sheet2").UsedRange.Offset(0).ClearContents
With Worksheets("Sheet1")
.Range("A:E").AutoFilter
'Array filter from NameList
.Range("A:J").AutoFilter Field:=3, Criteria1:=Application.Transpose(vName), _
Operator:=xlFilterValues
.Range("A:E").AutoFilter field:=2, Criteria1:="=String1" _
, Operator:=xlOr, Criteria2:="=string2"
.Range("A:E").AutoFilter field:=3, Criteria1:=">0", _
.Range("A:E").AutoFilter field:=5, Criteria1:="Number"
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("A1")
End With
End Sub
回答by Doug Glancy
Here's a different approach. The heart of it was created by turning on the Macro Recorder and filtering the columns per your specifications. Then there's a bit of code to copy the results. It will run faster than looping through each row and column:
这是一种不同的方法。它的核心是通过打开宏记录器并根据您的规范过滤列来创建的。然后有一些代码来复制结果。它将比循环遍历每一行和列运行得更快：
Sub FilterAndCopy()
Dim LastRow As Long
Sheets("Sheet2").UsedRange.Offset(0).ClearContents
With Worksheets("Sheet1")
.Range("$A:$E").AutoFilter
.Range("$A:$E").AutoFilter field:=1, Criteria1:="#N/A"
.Range("$A:$E").AutoFilter field:=2, Criteria1:="=String1", Operator:=xlOr, Criteria2:="=string2"
.Range("$A:$E").AutoFilter field:=3, Criteria1:=">0"
.Range("$A:$E").AutoFilter field:=5, Criteria1:="Number"
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("A1")
End With
End Sub
As a side note, your code has more loops and counter variables than necessary. You wouldn't need to loop through the columns, just through the rows. You'd then check the various cells of interest in that row, much like you did.
作为旁注，您的代码具有比必要更多的循环和计数器变量。您不需要遍历列，只需遍历行。然后，您将检查该行中感兴趣的各种单元格，就像您所做的那样。
