用于过滤列的 VBA [英] VBA for filtering columns

问题描述

我有一个类似数据库的大表，第一行包含标题.我想要基于列值的该表行的子集.两个问题:

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 我想遍历列，当所有必需列的值都匹配时，将整行复制到新工作表中.

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.

2) 行的子集基于列表.我刚刚读到我可以将 Autofilter 与数组一起使用.是否可以从一列输入这个数组而不是在 VBA 代码中手动输入它?我使用的列表由 200 个不同的字符串组成，并且会定期更新.

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.

其中 CritList 是字符串列表.我仍然需要弄清楚怎么做，但现在我离开了办公室，所以明天更多.

Where CritList is the list of strings. I still need to figure out how, but now I leave the office, so more tomorrow.

EDIT1 感谢@DougGlancy；自动过滤现在有效.这是他漂亮的代码(我只添加了数组过滤器).

EDIT1 Thanks to @DougGlancy; the autofiltering works now. Here is his beautiful code (I only added the array-filter).

EDIT2 包括一个更精细的数组过滤器，其中 NameList 是我想要过滤的列表.现在一切正常！

EDIT2 Included a more elaborate array-filter, where NameList is the list I would like to filter for. Now it all works!

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

推荐答案

这是一种不同的方法.它的核心是通过打开宏记录器并根据您的规范过滤列来创建的.然后有一些代码来复制结果.它会比遍历每一行和每一列运行得更快:

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.

这篇关于用于过滤列的 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！