使用Excel VBA逐个筛选列值（不同） [英] Filter column values (different) one by one using Excel VBA

问题描述

我正在尝试逐个过滤列的值。经过过滤器的值每次都不同。有没有一种方法可以捕捉到这个。

I am trying to filter values of a column one by one. Values subjected to filter comes different every time. Is there a way that a macro can capture this out.

Sub Macro1()
    Sheets("Open").Select
    'invoice value less than 0
    ActiveSheet.Range("$A$1:$R$2700").AutoFilter Field:=11, Criteria1:="<0", _
        Operator:=xlAnd
    'Selected Country
    ActiveSheet.Range("$A$1:$R$2700").AutoFilter Field:=3, Criteria1:="ARG"
    'Selected invoice#
    ActiveSheet.Range("$A$1:$R$2700").AutoFilter Field:=7, Criteria1:="1007225"
    Range("G528").Select
    ActiveSheet.Range("$A$1:$R$2700").AutoFilter Field:=7, Criteria1:="1015678"
    ActiveSheet.Range("$A$1:$R$2700").AutoFilter Field:=7, Criteria1:="1018523"
    Range("G1").Select
    ActiveSheet.Range("$A$1:$R$2700").AutoFilter Field:=7, Criteria1:="1018962"
End Sub

推荐答案

c> .AutoFilter 字段嵌入到变体数组中，并使用th e 等级与 LBound 和 UBound 。

Stuff your .AutoFilter fields into a nested variant array and look through the sets using the rank with LBound and UBound.

Sub a1Multi_Filter()
    Dim v As Long, vCRITs As Variant
    Dim vKey As Variant, dCOL7s As New Scripting.Dictionary

    dCOL7s.CompareMode = BinaryCompare

    vCRITs = Array(Array(11, "<0"), _
                   Array(3, "ARG"), _
                   Array(7, "1007225"), _
                   Array(7, "1015678"), _
                   Array(7, "1018523"), _
                   Array(7, "1018962"))

    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter
        For v = LBound(vCRITs, 1) To UBound(vCRITs, 1)
            .AutoFilter Field:=vCRITs(v)(0), Criteria1:=vCRITs(v)(1)

            'pause for effect and processing
             MsgBox "array presets" & Chr(10) & "filtered " & .Address(0, 0) & " on column " & Chr(64 + vCRITs(v)(0)) & " for " & vCRITs(v)(1)

            .AutoFilter Field:=vCRITs(v)(0)
        Next v
        .AutoFilter

        For v = 2 To .Rows.Count
            If CBool(Len(.Cells(v, 7).Value)) And Not dCOL7s.Exists(.Cells(v, 7).Value) Then _
                dCOL7s.Add Key:=.Cells(v, 7).Value, Item:=7
        Next v

        For Each vKey In dCOL7s.Keys  'v = 0 To (dCOL7s.Count - 1)
            .AutoFilter Field:=dCOL7s.Item(vKey), Criteria1:=vKey

            'pause for effect and processing
            MsgBox "column 7 special" & Chr(10) & "filtered " & .Address(0, 0) & " on column " & Chr(64 + dCOL7s.Item(vKey)) & " for " & vKey

            .AutoFilter Field:=dCOL7s.Item(vKey)
        Next vKey
    End With
    dCOL7s.RemoveAll: Set dCOL7s = Nothing
End Sub

我给你留下了足够的空间，消息文本应该有助于解释

I've left you plenty of room for processing and the message text should help to explain the elements of the variant array(s).

在设置了字段索引和条件参数之后， For ... Next 循环使用第一级的下限和上限边界进行处理。每个集合依次应用，我已经暂停了一个msgbox，显示自动过滤器范围，过滤字段和过滤条件。 处理之后，系统会自动清除该区域的自动过滤器，以准备下一个序列。

After setting up the pairs of field indexes and criteria arguments, the For ... Next loop uses the first rank's lower and upper boundaries for the limits of processing. Each set is applied in turn and I've paused with a msgbox showing the autofilter range, filtered field and filter criteria. After 'processing' the autofilter for that field is cleared in preparation for the next in the sequence.

附录：

我将列G中的唯一项目的集合添加到脚本字典中，然后循环使用字典键和项，使用每对作为过滤器和条件在同一个 .AutoFilter 。要使用脚本字典，您需要进入VBE的工具►参考并将 Microsoft脚本运行时版本添加到项目中。这只需要加一次;不是每个可能运行的计算机，而是如果启动了一个使用脚本字典的新项目，那么它也必须添加到该项目中。

I've added the collection of unique items from column G into a scripting dictionary and then cycled through the dictionary keys and items, using each pair as filter and criteria in the same .AutoFilter. To use a scripting dictionary, you need to go into the VBE's Tools ► References and add Microsoft Scripting Runtime to the project. This only needs to be added once; not for every computer that this may run on but if a new project is started that uses a scripting dictionary, it will have to be added to that project as well.

这篇关于使用Excel VBA逐个筛选列值（不同）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！