基于范围VBA的过滤列 [英] Filtering Column Based on Range VBA

问题描述

我正在处理大型机的数据，而不是不同于此处提供的数据。在这种情况下，我所有的字符串类似于我的标准和我的目标数据集的100-AAA。我已经搜索了一个类似的解决方案，但是我还没有发现一个直接过滤范围问题的VBA在SO。

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.

我试图过滤大型数据集基于标准范围。最初，我开始在单独的课程/子程序中收集集合，但是我在一个子例程中压缩到使用范围，如下所示：

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

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

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.

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

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

推荐答案

您当前的行：

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

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

is trying to filter your first field by a Range object, not by each value in the 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):

• 我学习了正确的语法，通过使用宏录像机对选择的对象进行过滤。


• 此网站帮助我了解数组技巧：将数组传递给过滤器条件


• 我还清理了代码，使其更有效率和更快速度，通过摆脱无用的select语句和这样。 （对于这一点代码来说不算什么，但是从这个练习中脱颖而出可以帮助你实现更大的项目！）

代码下面：

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

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