Advanced Filter Criteria

[jstevens]

Is it possibe to declare the "critereia range" as a VBA variable? I know how to use VBA to extract data using advanced filters but thought that this may be an interesting alternative.

Example of code:
oRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ThisWorkbook.Sheets("Sheet2").Range("P2:P3"), _
CopyToRange:=ThisWorkbook.Sheets("Sheet2").Range("A2"), Unique:=True

In the example above the "criteria range" is set to the range P2:P3 within the sheet. Pehaps something like: CriteriaRange:="Depts,1234"

Regards,
John

[sdckapr]

Something like this?

Dim rDepts1234 as range
rDepts1234 = ThisWorkbook.Sheets("Sheet2").Range("P2:P3")
oRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rDepts1234, _
CopyToRange:=ThisWorkbook.Sheets("Sheet2").Range("A2"), Unique:=True

Steve

[HansV]

In addition to Steve's reply: the CriteriaRange argument *must* be a reference to a literal range or to a named range, either directly or through a variable as in Steve's example. It cannot be an array.