Excel VBA Autofilter contains with multiple criteria

Question

I need to filter a range with multiple criteria with operator Contains

The following code works very well (2 critera):

shData.UsedRange.AutoFilter field:=2, Criteria1:=Array("*a*", "*b*"), Operator:=xlFilterValues

But, not sure why the below code doesn't work (more than 2 criteria):

shData.UsedRange.AutoFilter field:=2, Criteria1:=Array("*a*", "*b*", "*c*"), Operator:=xlFilterValues

Answer 1

It's a limitation of autofilters. You can't use more than two contains filters in the UI either.

You can either use an Advanced filter instead, or you can create an array of the values that match your criteria and filter using that:

Sub MultiContainsAutofilter()
    Dim vData
    Dim shData                As Worksheet
    Dim d                     As Object
    Dim i                     As Long

    Set shData = ActiveSheet
    vData = shData.UsedRange.Columns(2)

    Set d = CreateObject("Scripting.Dictionary")

    For i = LBound(vData, 1) To UBound(vData, 1)
        If UCase$(vData(i, 1)) Like "*A*" Or UCase$(vData(i, 1)) Like "*B*" Or UCase$(vData(i, 1)) Like "*C*" Then
            d(vData(i, 1)) = Empty
        End If
    Next i

    If d.Count > 0 Then shData.UsedRange.AutoFilter Field:=2, Criteria1:=d.keys, Operator:=xlFilterValues
End Sub