Applying Like Condition In Advance Filter

If you want to create a search option in Excel with Like condition using Advance Filter

Try this code -


Sub apply_filter()

Dim srchrange As Range
Dim criteriarange As Range


'this will remove if any filters applied earlier
If Sheets("Data").FilterMode Then
    Sheets("Data").ShowAllData
End If

Set srchrange = Sheets("Data").Range("a1:b" & Sheets("Data").Range("a1").End(xlDown).Row) ' Source data

Set criteriarange = Sheets("Search").Range("A1:A" & Sheets("Search").Range("A1").End(xlDown).Row) ' criteria

Sheets("Output").UsedRange.Clear
'apply filter and paste the result on output sheet
srchrange.AdvancedFilter Action:=xlFilterCopy, criteriarange:= _
criteriarange, CopyToRange:=Sheets("Output").Range("a1"), Unique:=False


Sheets("Output").Select
Range("a1").Select
End Sub


Download working file