Saturday, June 4, 2011

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

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

End Sub

Download working file 

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...