AdvancedFilter Method [Excel 2007 Developer Reference]

Switch View :
Classic
Lightweight
ScriptFree
Range.AdvancedFilter Method
Filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used.

Syntax

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

expression   A variable that represents a Range object.

Parameters

NameRequired/OptionalData TypeDescription
ActionRequiredXlFilterActionOne of the constants of XlFilterAction specifying whether to make a copy or filter the list in place.
CriteriaRangeOptionalVariantThe criteria range. If this argument is omitted, there are no criteria.
CopyToRangeOptionalVariantThe destination range for the copied rows if Action is xlFilterCopy. Otherwise, this argument is ignored.
UniqueOptionalVariantTrue to filter unique records only. False to filter all records that meet the criteria. The default value is False.

Return Value
Variant

Example

This example filters a database (named "Database") based on a criteria range named "Criteria."

Visual Basic for Applications
Range("Database").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Range("Criteria")

See Also


Community Content

cd112358
More Details
I'm skipping the intuitive stuff and getting to the parts that I was stuck at for some time...

Here's how I would write the VBA to use this method
'from my head, may be syntax typo but you get the idea
Set srcWS = ThisWorkbook.sheets(1)
Set crWS = Thisworkbook.sheets(2)
Set dstWS = ThisWorkbook.sheets(3)

'suppose A1: Name, B1: Address, C1: Phone, and each column has records
Set srcRange = srcWS.Cells(1,1).currentRegion 'get data
'let dstRange be where to copy the filtered data
Set dstRange = dstWS.Range("A1")
'suppose on crWS A1: Name, A2: Bob, A3: Alice is criteria
Set crRange = ws.Range("A1:A3")
srcRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=crRange
' now the interesting part... the filtered output are visible while filtered out records are invisible
Set fltRange = srcRange.SpecialCells(xlCellTypeVisible)
fltRange.Copy(dstRange) 'copy query records some where
' return the src sheet to original state
srcWS.ShowAllData

'I got to this step and decided to repeat the steps above to apply yet more filters to my record.
'Perhaps there is another short cut for filtering many criteria with one line of code but this solution
'is fast enough to get everything done.