VBA Advanced Filter in Excel — Extract Unique Values and Filter to a New Range (Without a Loop)

TL;DR — Range.AdvancedFilter is the only filter that hands you data, not a view. In one call it can copy a unique list or a criteria-matched set of rows to another location, without a loop and without deleting anything. Two ideas unlock it. First, Action chooses between xlFilterInPlace (hides rows, like AutoFilter) and xlFilterCopy (copies the results to a CopyToRange — the powerful mode). Second, its "WHERE clause" is not code — it is a criteria range, a small block of cells whose header must match the source header exactly. Add Unique:=True and you get a distinct list in one line, with the original left untouched.

' Extract a distinct list of Region values from column B into column E. Non-destructive.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("B1:B1000").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=ws.Range("E1"), _   ' E1 must hold the SAME header as B1
        Unique:=True                     ' one of each value, source unchanged

AdvancedFilter is the tool people reach for last and should reach for first. When you need a result as data — a distinct list, an export of the rows matching a condition, a de-duplicated copy that leaves the original intact — the instinct is to write a For Each loop with an If and a Dictionary. Excel already has a query engine for exactly this. This guide is built around its two defining ideas: it can copy instead of hide, and its criteria live in cells.

What you'll learn

• The mental model — SQL-lite for a range: extract, don't just view
• In place vs copy — xlFilterInPlace versus xlFilterCopy
• The criteria range — your WHERE clause lives in cells, not in code
• Unique:=True — a distinct list without deleting anything
• The header-match trap that returns empty output
• AdvancedFilter vs AutoFilter vs RemoveDuplicates

The mental model: SQL-lite for a range

Think of AdvancedFilter as a tiny query engine bolted onto a range. AutoFilter answers "which rows do I want to see?" and hides the rest in place. AdvancedFilter answers a bigger question: "give me the rows that match — as a new set of data I can put somewhere and use." It can filter in place too, but its reason to exist is the copy mode: it takes a source range, an optional set of conditions, and writes the matching (or unique) rows to a destination you choose.

That reframing matters because it replaces a whole category of loops. "Get the unique customers," "pull every order over 1000 to a report sheet," "list the distinct product codes" — these are one-call extractions, not iteration. Hold the picture — AdvancedFilter produces data, AutoFilter produces a view — and you will know which one a task needs.

In place vs copy: xlFilterInPlace vs xlFilterCopy

The Action argument is the fork in the road:

• xlFilterInPlace hides the non-matching rows right in the source range, exactly like AutoFilter. The data does not move; you get a filtered view. To clear it afterwards you call ws.ShowAllData.
• xlFilterCopy leaves the source alone and copies the matching rows to CopyToRange. This is the mode that makes AdvancedFilter special — it is non-destructive and produces a separate, usable block of data.

' Copy mode - the one you'll usually want.
ws.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=ws.Range("H1:H2"), _   ' the WHERE clause (see below)
        CopyToRange:=ws.Range("K1"), _        ' where the results land
        Unique:=False

If you pass xlFilterCopy you must provide CopyToRange; if you pass xlFilterInPlace you must not (Excel errors either way if you mix them up). When in doubt, copy — it never touches your original.

The criteria range: your WHERE clause lives in cells

This is the part that feels alien coming from other languages: AdvancedFilter does not take a condition as a string or an expression. Its conditions live in a criteria range — a small block of worksheet cells. The top row holds column headers that match the source, and the rows beneath hold the conditions.

   H            I
1  Region       Amount
2  West         >1000

That criteria range says "Region is West AND Amount > 1000." Conditions on the same row are AND; conditions stacked on separate rows are OR. So two rows —

   H
1  Region
2  West
3  East

— means "Region is West OR East." You can build this block in cells your macro owns (a scratch area, or a hidden sheet) and point CriteriaRange at it:

ws.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=ws.Range("H1:I2"), _
        CopyToRange:=ws.Range("K1")

Omit CriteriaRange entirely and no condition is applied — which, combined with Unique:=True, is exactly how you get a plain distinct list.

Unique:=True — a distinct list without deleting anything

Set Unique:=True and AdvancedFilter returns one of each distinct row (or value, for a single column) — the same result as Remove Duplicates, except it copies the uniques out and leaves the source intact. That single property is why AdvancedFilter is the safe way to dedupe:

' Distinct customer names into column K, original list untouched.
ws.Range("C1:C5000").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=ws.Range("K1"), _
        Unique:=True

Where RemoveDuplicates deletes in place with no undo, this hands you a fresh, deduplicated list and never risks the original. When someone asks "how do I get unique values in VBA without wrecking the data," this is the answer.

The header-match trap that returns empty output

The number-one reason AdvancedFilter "returns nothing" is a header mismatch. Both the CriteriaRange header and the CopyToRange header must match the source headers exactly — same spelling, same spacing, same case-insensitive text. A criteria header of "Reigon" (typo), or a CopyToRange whose top cell is blank or mislabelled, produces empty or wrong output without raising an error.

Two habits prevent it:

• Build the criteria and copy-to headers by copying the real header cells, never by retyping them.
• If you filter to another sheet, remember an old rule: classic AdvancedFilter wants the CopyToRange on the active sheet. The robust pattern is to run the filter from the sheet the results land on, or to filter into a scratch range on the source sheet and move the result afterwards.

When output comes back empty, check the headers before you check anything else.

AdvancedFilter vs AutoFilter vs RemoveDuplicates

They overlap enough to confuse and differ enough to matter:

• AutoFilter — a view. Hides rows in place so the user sees a subset; nothing is copied or removed. Reach for it to show a filtered range.
• AdvancedFilter (copy mode) — a query. Extracts matching or unique rows to a new location, non-destructively. Reach for it when you need the result as data.
• RemoveDuplicates — a deletion. Shrinks the data in place, keeps the first of each, no undo. Reach for it only when you truly want the source itself smaller and you have a backup.

The line worth remembering: AutoFilter shows, RemoveDuplicates destroys, and AdvancedFilter is the one that extracts — a distinct list or a criteria-matched copy — without touching the original. When you catch yourself writing a loop to build a filtered or unique list, that is AdvancedFilter.

How ExcelMaster helps

AdvancedFilter is powerful precisely because it is fiddly: the in-place-vs-copy fork, the criteria range that has to live in cells with headers that match to the letter, the copy-to destination and its active-sheet rule, the Unique flag. Get any header wrong and it returns nothing, silently.

ExcelMaster lets you describe the query instead. Say "pull every order over 1000 from the West region to a new sheet" or "give me the distinct list of product codes," and it lays out the criteria range with headers copied from the source, chooses copy mode with a valid CopyToRange, sets Unique when you want distinct values, and leaves the original data intact. You keep the workbook and the code; you skip the half-hour of wondering why a mistyped criteria header returned an empty result.

Frequently asked questions

How do I use Advanced Filter in VBA?

Call AdvancedFilter on the source range: choose Action:=xlFilterCopy to copy results elsewhere (with a CopyToRange) or xlFilterInPlace to hide non-matching rows. Provide a CriteriaRange for conditions, or set Unique:=True with no criteria to extract a distinct list. In copy mode the source data is left unchanged.

How do I extract unique values with VBA Advanced Filter?

Use copy mode with Unique:=True and no criteria: rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("E1"), Unique:=True. It writes one of each distinct value to the destination and leaves the source intact — unlike RemoveDuplicates, which deletes in place. The CopyToRange header must match the source header.

Why does VBA Advanced Filter return nothing?

Almost always a header mismatch. The CriteriaRange and CopyToRange top-row headers must match the source headers exactly (a typo or blank header returns empty output with no error). Copy the real header cells rather than retyping them, and make sure a copy-mode filter has a valid CopyToRange.

What is the difference between AutoFilter and Advanced Filter in VBA?

AutoFilter produces a view — it hides non-matching rows in place and copies nothing. AdvancedFilter produces data — in copy mode it extracts matching or unique rows to a new location without altering the source, and it supports complex AND/OR criteria and unique extraction that AutoFilter cannot.

How do criteria work in VBA Advanced Filter?

Criteria live in a range of cells, not in code. The top row holds headers that match the source; conditions on the same row are combined with AND, and conditions on separate rows are combined with OR. Point CriteriaRange at that block. Omit it to apply no condition (useful with Unique:=True).

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-10.

Related guides: VBA AutoFilter · VBA Remove Duplicates · VBA Sort · VBA Find · VBA Range