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

smallxyz

Active Member
Joined
Jul 27, 2015
Messages
368
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!


Code:
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!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,668
Office Version
365
Platform
Windows
You can only use Autofilter with two matches, unless you are looking for exact matches.
Have a look at Advanced filter instead.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
With the following you can auto-filter more than 2 words

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,064
Messages
5,466,380
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top