Can AutoFilter take both inclusive and non-inclusive wildcards from Dictionary keys?

Question

I have been looking for a way to filter an Excel spreadsheet with more than two wildcards. I asked on StackOverflow previously if I could put more than two wildcards in to AutoFilter in VBA directly instead of using advanced filter in the worksheet, as my macros are mostly used via PowerShell scripts, which pass input through. These wildcards are used to filter various spreadsheets and the result is saved.

A very helpful user came up with an answer gave an example macro using Dictionary keys, which I then extended to accept an array as input, and then loop through all the items in the array to filter as wildcards. Excellent, working as intended!

Now I want to extend this to pass more specific wildcards I want to exclude. Say for example, I want to include "A*" and "B*", but not "BB*", so that "BA*" would still be there, for example. Could the below macro work with maybe passing <>BB* through?

The hierArray only ever contains a list of simple strings consisting of a maximum 10 (but rarely more than 3 characters).

Public Function multiHier(hierArray As Variant)

Dim v As Long, vVALs As Variant, dVALs As Object
Dim colNum As Long, hierLen As Integer, hier As Variant
Dim rng As Range

Set dVALs = CreateObject("Scripting.Dictionary")
dVALs.comparemode = vbTextCompare
colNum = Application.Match("*ierarchy*", Range("A1:Z1"), 0)

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

                For Each hier In hierArray
                    hierLen = Len(hier)

                    Select Case UCase(Left(vVALs(v, 1), hierLen))
                        Case hier
                            dVALs.Add Key:=vVALs(v, 1), item:=vVALs(v, 1)
                        Case Else
                   End Select

               Next hier
            End If
        Next v

        If CBool(dVALs.Count) Then
            'populated the dictionary; now use the keys
            .AutoFilter Field:=colNum, Criteria1:=dVALs.keys, Operator:=xlFilterValues

            Set rng = Worksheets(1).AutoFilter.Range
            multiHier = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
        Else
            multiHier = 0
        End If

    End With

End With

dVALs.RemoveAll: Set dVALs = Nothing

End Function

Watersick

So hierArray would be something like hierArray = Array("A•", "B•", "<>BB•") ...? – Rochette

At the moment the array just looks like hierArray = Array("A","B","CC") etc. But thanks to the macro works as a wildcard. If just adding <> to those excluded it, that works fine by me. Assumed it wouldn't be that simple! – Watersick

It won't but the method used to construct the string needs to be finalized in order to write code that parses the string and converts it into working operation(s). – Rochette

True. The array looking like ("A","B","!BB") or ("A","B","<>BB") etc I'm not too precious. – Watersick

Answer 1

I'm going to stick to the ! prefix for the discards as that is a single character.

    Dim h As Long, hstr As String   'put these at the top with the other var declarations

    For v = LBound(vVALs, 1) To UBound(vVALs, 1)
        For h = LBound(hierArray) To UBound(hierArray)   'I just prefer to work this way
            hstr = hierArray(h) & Chr(42)   'stick a * on the end
            If Left(hstr, 1) = Chr(33) And LCase(vVALs(v, 1)) Like LCase(Mid(hstr, 2)) Then     'starts with a ! and pattern matches the value
                'matched a discard pattern. check to see if it was previously added
                If dVALs.Exists(vVALs(v, 1)) Then _
                    dVALs.Remove vVALs(v, 1)    'get rid of it
                Exit For  'discarded. do not keep checking to add
            ElseIf LCase(vVALs(v, 1)) Like LCase(hstr) Then
                If NOT dVALs.Exists(vVALs(v, 1)) Then _
                    dVALs.Add Key:=vVALs(v, 1), Item:=vVALs(v, 1)
            End If
        Next h
    Next v

When creating the hierArray string, you can save a few cycles by putting the discard patterns first. That way, they will not get added and then subsequently removed.

Any further work in this areas would likely warrant switching to a full Regular Expression (regexp) pattern matching method.

Rochette

Great, thank you - will give it a go. My only concern discarding first, what if something like ABA,ABB,ABX are wanted, but not ABC? Does it not make sense to match AB then specifically exclude ABC? – Watersick

It would really depend on the number of possible matches for each scenario but it you discard first then you do not try other matches. You might match AB and then have to discard the same entry with !ABC later. If ABC is discarded first, it exits the loop (doesn't keep trying to add) so the remaining cycles are not run through. – Rochette

I've tested the above and get Run-time error '457': This key is already associated with an element of this collection pointing at line dVALs.Add Key:=vVALs(v, 1), item:=vVALs(v, 1) I've tested this with and without the exclude. – Watersick

My test data did not have duplicates within the potential matches. I've added one line of code to compensate for this. – Rochette

is it possible to modify this macro so that the code will run if it just gets sent 1 key? it expects an array (or Variant, which always tend to be an array, but could be one single string) so currently it mismatches. It falls over at the upper/lower bound checks, I'm not sure how to get around this for just 1 element. – Watersick