基于范围 VBA 的过滤列

Filtering Column Based on Range VBA

提问by HymanOrangeLantern

I am presently working with data from a mainframe, not unlike the data presented here. In this case, all I have are strings akin to 100-AAA for my criteria and for my target data set. I have searched for a similar solution, but I have not found a direct filter by range question for VBA on SO.

我目前正在处理来自大型机的数据，与此处提供的数据不同。在这种情况下，对于我的标准和目标数据集，我所拥有的只是类似于 100-AAA 的字符串。我已经搜索了类似的解决方案，但我没有在 SO 上找到 VBA 的按范围问题的直接过滤器。

I am attempting to filter a large dataset based on a range of criteria. Originally, I started playing with collections in separate classes/subroutines, but then I compressed to the use of ranges within a single subroutine, as follows:

我正在尝试根据一系列标准过滤大型数据集。最初，我开始在单独的类/子例程中使用集合，但后来我压缩到在单个子例程中使用范围，如下所示：

Sub FilterTest1()

Dim RngOne As Range
Dim LastCell As Long

Sheets("Criteria").Activate
Sheets("Criteria").Select
Range("A1").Select

LastCell = Application.WorksheetFunction.CountA(ActiveSheet.Range("A:A"))

Set RngOne = ActiveSheet.Range("A2:A" & LastCell)

Sheets("Sheet 1").Activate
Sheets("Sheet 1").Select

With ActiveSheet
    If .FilterMode Then
    .ShowAllData
    End If
End With

Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:=RngOne, Operator:=xlOr

End Sub

Unfortunately, my range of cells does not filter the data, even when using criteria such as Operator:=xlOr- it just displays the very last cell value used for the criteria. The VBA code does not fail - it just does not filter properly based on the range.

不幸的是，我的单元格范围不会过滤数据，即使使用诸如Operator:=xlOr- 它只显示用于该条件的最后一个单元格值。VBA 代码不会失败 - 它只是没有根据范围正确过滤。

I feel certain that there exists a simple solution, but I cannot quite see it.

我确信存在一个简单的解决方案，但我看不到它。

回答by Scott Holtzman

Your current line:

您当前的线路：

ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:=RngOne, Operator:=xlOr

is trying to filter your first field by a Rangeobject, not by each value in the range.

正在尝试按Range对象过滤您的第一个字段，而不是按范围中的每个值。

I've provided one way to do this below, by loading your values into an array and then passing the array to the filter criteria.

我在下面提供了一种方法来执行此操作，方法是将您的值加载到一个数组中，然后将该数组传递给过滤条件。

A few points worth noting to help you (and anyone else in the future):

一些值得注意的要点可以帮助您（以及将来的任何其他人）：

• I learned of the correct syntax to filter on a selection of objects by using the macro recorder.
• This site helped me learn about the array trick: Pass Array to Filter Criteria
• I also cleaned up your code to make it more efficient and faster, by getting rid of useless select statements and such. (Not a big deal for this little bit of code, but getting out of that practice will help you for bigger projects!)

• 我学会了使用宏记录器过滤所选对象的正确语法。
• 这个站点帮助我了解了数组技巧：Pass Array to Filter Criteria
• 我还清理了您的代码，通过删除无用的 select 语句等来提高效率和速度。（对于这一点代码来说没什么大不了的，但是摆脱这种做法将有助于您完成更大的项目！）

Code Below:

代码如下：

Sub FilterTest1()

Dim RngOne As Range, cell As Range
Dim LastCell As Long
Dim arrList() As String, lngCnt As Long

With Sheets("Criteria")
    LastCell = .Range("A" & Sheets("Criteria").Rows.Count).End(xlUp).Row
    Set RngOne = .Range("A2:A" & LastCell)
End With

'load values into an array
lngCnt = 0
For Each cell In RngOne
    ReDim Preserve arrList(lngCnt)
    arrList(lngCnt) = cell.Text
    lngCnt = lngCnt + 1
Next


With Sheets("Sheet 1")

    If .FilterMode Then .ShowAllData

    .Range("A:A").AutoFilter Field:=1, Criteria1:=arrList, Operator:=xlFilterValues

End With

End Sub

回答by Rory

A thing to note on is that the Criteria1requires string variables as the argument. You can assign a range to an array then pass the stringarray to the criteria. Another note is that to pass an array to the criteria, to my knowledge, it has to be in a row, not a column eg. StringArray(0,3)instead of StringArray(3,0). Hence why you were getting the last value in your range. Just use Application.Transpose(StringArray)to convert to a row.

需要注意的一点是Criteria1需要字符串变量作为参数。您可以为数组分配一个范围，然后将该string数组传递给条件。另一个注意事项是，将数组传递给条件，据我所知，它必须在一行中，而不是一列，例如。StringArray(0,3)而不是StringArray(3,0). 因此，为什么您获得了范围内的最后一个值。仅用于Application.Transpose(StringArray)转换为一行。

This little bit of code works for me, and is easily expanded to bigger arrays.

这段代码对我有用，并且很容易扩展到更大的数组。

Sub test()
Dim StringArray(0, 1 To 3) As String 'This is a fixed dimension of an array, you can easily change to dynamic by using the ReDim statement.
ActiveSheet.AutoFilterMode = False 'To reset autofilter

Range("D3:D5").Select

For c = 1 To 3 'Step through the range that you wish to use as the criteria.
StringArray(0, c) = CStr(Selection(c, 1).Value)
Next

Range("A:A").AutoFilter Field:=1, Criteria1:=StringArray, Operator:=xlFilterValues

End Sub