excel - AutoFilter based on an array, more than 3 elements

Question

EDIT: My variables contain wildcards. I saw something online that says that if I am using wildcards, the autofilter can contain only two conditions. Is this true? If so, this is likely my issue. Unfortunate. END EDIT

I am trying to filter my data to show if the data contains one of the six items in an array. The elements are String variables. My working code is:

With ActiveSheet
    .Columns("J").AutoFilter Field:=1, Criteria1:=Array(d3, d2), _
     Operator:=xlFilterValues
End With

The idea behind this works for what I wish to accomplish. However, I actually want 6 elements of the array, not just two. Unfortunately, when I try to add all six elements, nothing shows up.

 With ActiveSheet
    .Columns("J").AutoFilter Field:=1, Criteria1:=Array(d3, d2, d1, d21, d11, d31), Operator:=xlFilterValues
End With

Im not getting an error or anything. It's just that nothing shows up. Does anyone know how to fix this issue? I've tested the code with just two elements in the array with various combinations of the Strings (d1, d21, etc...) and they all work as intended, so the issue is not with the variables.

Answer 1

AutoFilter is limited to 2 criteria with wildcards (* or ?)

The 2 versions below will allow you to specify as many wildcards as you need

---

.

Version 1 - Loops through each wildcard applying the AutoFliter and combining the visible ranges

---

Option Explicit

Public Sub FilterRows3WildAF()      '(Optional ByVal showAll As Boolean = False)

    Const FILTER_COL = "A"
    Const WILDCARDS = "Name Street Address Number"  'cell starts with these 4 words

    Dim ws As Worksheet, wild As Variant, lr As Long, toShow As Range, itm As Variant

    Set ws = ActiveSheet
    wild = Split(WILDCARDS) 'will search for cells starting with: Name*, then Street*, etc
    Application.ScreenUpdating = False
    ws.Rows.Hidden = False

    With ws.Range(ws.Cells(1), ws.Cells(ws.Rows.Count, FILTER_COL).End(xlUp))
        lr = .Rows.Count
        Set toShow = .Cells(lr + 1, FILTER_COL)
        For Each itm In wild
            .AutoFilter Field:=1, Criteria1:=itm & "*", Operator:=xlFilterValues
            If .SpecialCells(xlCellTypeVisible).Cells.CountLarge > 1 Then
                Set toShow = Union(toShow, .Offset(1).SpecialCells(xlCellTypeVisible))
            End If
        Next
        .AutoFilter
        .Rows.Hidden = True
        toShow.EntireRow.Hidden = False
    End With
    Application.ScreenUpdating = True
End Sub

---

.

Version 2 - Loops through each cell, checking with InStr() if the wildcard exists

---

Public Sub FilterRows3WildInstr()   '(Optional ByVal showAll As Boolean = False)

    Const FILTER_COL = "A"
    Const WILDCARDS = "Name Street Address Number"  'cell starts with these 4 words

    Dim ws As Worksheet, wild As Variant, lr As Long, arr As Variant
    Dim toHide As Range, r As Long, itm As Variant, found As Boolean

    Set ws = ActiveSheet
    wild = Split(WILDCARDS) 'will search for cells starting with: Name*, then Street*, etc
    ws.Rows.Hidden = False

    With ws.Range(ws.Cells(1), ws.Cells(ws.Rows.Count, FILTER_COL).End(xlUp))
        lr = .Rows.Count
        arr = .Value2
        Set toHide = .Cells(lr + 1, FILTER_COL)
        For r = 1 To UBound(arr)
            For Each itm In wild
                found = InStr(1, arr(r, 1), itm) > 0
                If found Then Exit For
            Next

            If Not found Then Set toHide = Union(toHide, .Cells(r, FILTER_COL))
        Next
        toHide.EntireRow.Hidden = True: .Rows(lr + 1).Hidden = False
    End With
End Sub