[VBA] extending Autofilter to filter with multiple words, using wildcard

[smallxyz]

The following codes only manage to filter for 2 words. (i.e. txt, txt)
When user inputs more than 2 words to filter (i.e. txt, txt, txt), an error just returns.
Is there any workaround to filter more than 2 words, for a particular column?

Thanks!

Sub Test()
    iTxt = InputBox("containing : t, t …", Default:=1)
    
    With ActiveSheet
        criteria_Arr = Split(iTxt, ",")
        For i = LBound(criteria_Arr ) To UBound(criteria_Arr )
            criteria_Arr (i) = "*" & criteria_Arr (i) & "*"
        Next
        
        selected_addr = Selection.CurrentRegion.Address
        .Range(selected_addr).AutoFilter Field:=1, Criteria1:=criteria_Arr , Operator:=xlFilterValues
    End With
End Sub

Thanks a lot!

 

[Fluff]

You can only use Autofilter with two matches, unless you are looking for exact matches.
Have a look at Advanced filter instead.

 

[DanteAmor]

With the following you can auto-filter more than 2 words

Sub Test()
    Dim r As Range, f As Range, cell As String, iTxt As Variant, arr() As Variant, n As Long, a As Variant
    
    Set r = Selection.CurrentRegion
    r.AutoFilter
    iTxt = InputBox("containing : t, t …", Default:=1)
    n = 0
    For Each a In Split(iTxt, ",")
        Set f = r.Find(a, LookIn:=xlValues, lookat:=xlPart)
        If Not f Is Nothing Then
            cell = f.Address
            Do
                ReDim Preserve arr(n)
                arr(n) = f.Value
                n = n + 1
                Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
        End If
    Next
    r.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
End Sub

 

[Kinjal Doshi]

Hi Dante,

This code works like a charm... but can you tweak the code as per my need?
In this code.. it does filter the words that I enter with comma separated value.. but it also filters if only one of the word is available in that cell.. I want it to filter only those cells where both the words are found in same cell

Now above is my list... If I filter with APPLE, KIWI then it will also filter Sr. No 4, 5 & 6 where only one of the strings are given. I want it to filter only if both the criteria's match in same cell

 

[Fluff]

@Kinjal Doshi
Please start a new thread for this question. Thanks