高级过滤标准可以在VBA中而不是范围内吗？ [英] Can Advanced Filter criteria be in the VBA rather than a range?

问题描述

在通过VBA尝试使用正常的AutoFilter方式尝试设置超过2个条件后，我已经了解到必须通过高级过滤器完成。

After trying in vain to set more than 2 criteria in a normal AutoFilter fashion via VBA, I have come to learn that it must be done via advanced filter.

违例示例：

Worksheets(1).Range("A1").AutoFilter Field:=ColNum, Criteria1:="A*", Operator:=xlOr, Criteria2:="B*", Operator:=xlOr, Criteria3:="C*"

我希望通过PowerShell脚本将标准传递给一个函数（而不是一个宏）。我有一切正常工作，正如预期的1个标准，但现在我想要3。

I am hoping to pass the criteria through to a function (rather than a macro) from a PowerShell script. I have it all working fine and as expected for 1 criteria, but now I'd like 3.

我想我可以改写一个宏来插入一个新的工作表，写入标准然后过滤该新的范围，但我宁愿首先检查首选方式。

I suppose I could instead write a macro to insert a new sheet, write in the criteria then filter on that new range but I'd rather check the preferred way first.

推荐答案

要过滤多个通配符，创建通配符匹配的变体数组，然后使用标准AutoFilter方法使用完整值的数组。您可以通过将字典对象放入其唯一索引属性中来最小化该数组。

To filter on multiple wildcards, create a variant array of wildcard matches and then use the array of full values with the standard AutoFilter method. You can minimize the array by putting a dictionary object to use with its unique index property.

考虑以下示例数据。

运行此代码。

Sub multiWildcards()
    Dim v As Long, vVALs As Variant, dVALs As Object
    Dim colNum As Long

    Set dVALs = CreateObject("Scripting.Dictionary")
    dVALs.comparemode = vbTextCompare
    colNum = 2 'column B

    With Worksheets(1)
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            vVALs = .Columns(colNum).Cells.Value2
            For v = LBound(vVALs, 1) To UBound(vVALs, 1)
                If Not dVALs.exists(vVALs(v, 1)) Then
                    Select Case UCase(Left(vVALs(v, 1), 1))
                        Case "A", "B", "C"
                            dVALs.Add Key:=vVALs(v, 1), Item:=vVALs(v, 1)
                        Case Else
                            'do nothing
                    End Select
                End If
            Next v

            If CBool(dVALs.Count) Then
                'populated the dictionary; now use the keys
                .AutoFilter Field:=colNum, Criteria1:=dVALs.keys, Operator:=xlFilterValues
            Else
                Debug.Print "Nothing to filter on; dictionary is empty"
            End If

            '.CurrentRegion is now filtered on A*, B*, C* in column B
            'do something with it
        End With
    End With

    dVALs.RemoveAll: Set dVALs = Nothing
End Sub

结果应该是：

< img src =https://i.stack.imgur.com/CCLOQ.pngalt =autofilter_multi_wilcard_results>

这些结果可以重复与许多其他通配符方案。 选择案例陈述是理想的，因为它支持像关键字，用于构建您的匹配集合。通过将值转储开始为常规变量数组，可以快速完成跨越甚至大行数据。

These results can be duplicated with many other wildcard scenarios. The Select Case statement is ideal as it supports the Like keyword for building your collection of matches. By starting with a value dump into a regular variant array, cycling through even large rows of data can be done quickly.

这篇关于高级过滤标准可以在VBA中而不是范围内吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！