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

问题描述

我有一个类似数据库的工作表，第一行包含标题。我希望基于列值的这个表的一行子集。两个问题：

1）VBA-wise我想循环遍历列，当所有必需列的值都匹配时，将整个行复制到新的表中。

2）行的子集基于列表。我只是读了我可以使用Autofilter与一个数组。是否可以从列中输入该数组，而不是在VBA代码中手动输入？我使用的列表包含200个不同的字符串，并将定期更新。

其中CritList是字符串列表。我还需要弄清楚如何，但现在我离开办公室，明天更多。

EDIT1 感谢@DougGlancy;自动过滤工作现在工作。这是他美丽的代码（我只添加了阵列过滤器）。

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 
与工作表（Sheet1）
 .Range（A：E）。AutoFilter 
 
'ArrayList from NameList 
 .Range（A：J）。AutoFilter字段：= 3，Criteria1：= Application.Transpose（vName），_ 
运算符：= xlFilterValues 
 
 .Range（A：E）。AutoFilter字段：= 2，Criteria1：== String1_ 
，运算符：= xlOr，Criteria2：== string2
。 （A：E）AutoFilter字段：= 3，Criteria1：=> 0，_ 
 .Range（A：E）。AutoFilter字段：= 5，Criteria1：=Number 
 
 LastRow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 .Range（A1：A& LastRow）.SpecialCells（xlCellTypeVisible ）.EntireRow.Copy _ 
目的地：=表（Sheet2）。范围（A1）
 
结束
 End Sub 
  

解决方案

这是一个不同的方法。它的核心是通过打开宏记录器并根据您的规格过滤列。然后有一些代码来复制结果。它将比循环遍历每行和列运行得快：

  Sub FilterAndCopy（）
 Dim LastRow As Long 
 
表格（Sheet2）UsedRange.Offset（0）.ClearContents 
带有工作表（Sheet1）
 .Range（$ A：$ E）。AutoFilter 
 .Range（$ A：$ E）AutoFilter字段：= 1，Criteria1：=＃N / A
 .Range（$ A：$ E 2，Criteria1：== String1，运算符：= xlOr，Criteria2：== string2
 .Range（$ A：$ E）AutoFilter字段：= 3，Criteria1：=> 0
 .Range（$ A：$ E）。AutoFilter字段：= 5，Criteria1：=Number
 LastRow = .Range（A& .Rows.Count）。 End（xlUp）.Row 
 .Range（A1：A& LastRow）.SpecialCells（xlCellTypeVisible）.EntireRow.Copy _ 
 Destination：= Sheets（Sheet2）。Range ）
结束
 End Sub 
  

作为附注，您的代码具有比必要更多的循环和反变量。您不需要循环遍历列，只需遍历行。然后，您可以检查该行中感兴趣的各种单元格，就像您一样。

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.

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.

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

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

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屋！