Excel VBA Advanced Filter: Using Multiple Criteria

VBA & macros · Updated November 19, 2026

To use multiple criteria with Excel VBA Advanced Filter, put conditions that must all be true on the same criteria row, and put alternative conditions on separate rows. Then call the source range’s AdvancedFilter method, specifying the criteria range and, if you want copied results, a destination header range.

What Advanced Filter does

Excel’s Advanced Filter applies criteria to a structured list with a header row. Unlike a basic AutoFilter, it can use a separate criteria range, express AND and OR logic, and copy matching records to another location. VBA can run the same operation repeatedly, which is useful for recurring reports or buttons that refresh a filtered extract.

The source list should be a single rectangular range: one header row followed by records, with no completely blank rows or columns splitting the list. Each source column needs a header. Use clear, unique column names; the criteria range refers to those names, and copied output headings need to match the source headings exactly.

Advanced Filter does not require a special worksheet table, although a formatted Excel Table can make data easier to manage. The VBA example below uses a fixed set of worksheet names and columns to make the range relationships explicit.

How AND and OR criteria are arranged

The shape of the criteria range determines the logic. A criteria row is an AND group: every condition in that row must be met. Separate criteria rows are OR alternatives: a record is returned if it matches any one of those rows. A blank criteria cell places no condition on that field for that row.

Suppose the data has columns Region, Product, Units, and Status. A criteria range with these headings and rows means “West and at least 100 units, or West and Open status”:

Region Units Status
West >=100 (blank)
West (blank) Open

The first row requires both Region to be West and Units to be at least 100. The second requires West and Open, regardless of Units. To express “West or East,” put West and East on separate rows under Region. To require two conditions on the same field, such as Units greater than 50 and less than 200, use that field’s heading in two adjacent criteria columns on the same row, with >50 and <200 beneath them.

Set up the worksheet ranges

For a repeatable macro, keep the source data, criteria, and results clearly separated. For the example code, arrange the workbook like this:

  • Data: headers in A1:D1 and records in A2:D onward. The assumed headings are Region, Product, Units, and Status.
  • Criteria: the three headings Region, Units, and Status in A1:C1, followed by the two criteria rows shown above in A2:C3.
  • Results: the four source headings, in the same spelling and order, in A1:D1. The macro writes matching records below them.

Criteria headings must be the same as the corresponding source field names. They do not have to sit in the same column positions as those fields, and you can omit fields that have no criteria. For copied results, the destination headings tell Advanced Filter which source columns to return; matching all four headings returns all four fields.

Run Advanced Filter from VBA

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro into the module. If the Developer tab is visible, Developer > Visual Basic also opens the editor. The macro assumes the three worksheet names and range layouts described above. Change those names or ranges if your workbook differs.

Option Explicit

Sub CopyAdvancedFilterResults()
    Dim wsData As Worksheet
    Dim wsCriteria As Worksheet
    Dim wsOutput As Worksheet
    Dim sourceRange As Range
    Dim criteriaRange As Range
    Dim outputHeaders As Range
    Dim lastRow As Long

    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsCriteria = ThisWorkbook.Worksheets("Criteria")
    Set wsOutput = ThisWorkbook.Worksheets("Results")

    ' Assumes column A is populated for every data record.
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    Set sourceRange = wsData.Range("A1:D"amp; lastRow)
    Set criteriaRange = wsCriteria.Range("A1:C3")
    Set outputHeaders = wsOutput.Range("A1:D1")

    ' Clear old output values but retain the destination headers.
    wsOutput.Range("A2:D"amp; wsOutput.Rows.Count).ClearContents

    sourceRange.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=criteriaRange, _
        CopyToRange:=outputHeaders, _
        Unique:=False
End Sub

In the two range-building lines, the ampersand joins a column address to the last-row number; in VBA it is written as the ampersand operator. The HTML representation above displays it as &. The macro finds the last used row by looking up column A, so that column must be filled for every record; if another column is the reliable one, change the column letter in the last-row expression. The source range includes its header, as Advanced Filter requires.

Set Unique:=False to keep duplicate matching records. Setting it to True asks Advanced Filter to return unique records based on the fields in the list or copied output, so use that only when removing duplicates is intended. The code clears existing values in the output area before copying new results; it preserves the row-one headings.

Use formula criteria for more complex tests

Criteria cells can contain comparison operators such as >=100, and Advanced Filter also supports formula-based criteria. Formula criteria are helpful when a rule combines fields in a way that the ordinary criteria grid cannot express. For example, use a custom heading such as Include? in the criteria range, not a source field name. With data headers in A1:D1 and the first record in row 2, place this formula in the criteria cell below that custom heading: =AND(C2>=100,D2="Open").

This criterion selects rows where Units in column C is at least 100 and Status in column D is Open. The formula reference must be written for the first data row and left relative so Excel can evaluate it for subsequent records. If your data begins on another row or its fields are in other columns, adjust the references. Formula criteria are a common source of confusing results: the criteria heading must be different from every source heading, and the formula should evaluate to TRUE or FALSE for each record.

Choose copy results or filter in place

The VBA method accepts different actions depending on what you need:

  • xlFilterCopy copies matching rows to the range named by CopyToRange. This is usually preferable for a report because it leaves the source list intact.
  • xlFilterInPlace hides records that do not meet the criteria in the original list. It does not create a separate extract; clear the filter when you want to show all rows again.

For copied results, the destination range should start with the output field headings, and those headings must match source headings. For in-place filtering, you do not need a separate copy destination. In either case, use a well-formed criteria range with the field headings in its first row.

Common errors and practical fixes

A macro that returns no rows or the wrong rows is often a range-layout problem rather than a VBA problem. Check the following before changing the code:

  • Criteria heading mismatch: remove extra spaces and confirm the criteria heading matches the source header exactly. A heading such as Units and a heading such as Unit are different.
  • AND/OR reversed: conditions placed on the same row are AND; separate rows are OR. Reposition the criteria instead of trying to repair the logic in VBA.
  • Wrong source boundary: confirm the source range includes every record and its header row, and that the last-row column is populated throughout.
  • Bad copy headings: check that each Results heading you want returned matches a source heading. A typo can prevent the expected field from being copied.
  • Numbers or dates stored as text: values that look numeric may not compare as numbers if they were imported as text. Convert or clean the source data, then check the criteria again.
  • Old results remain: if you do not clear or replace the previous output, readers may mistake stale rows for current matches. The sample clears its output columns first.

Text criteria are generally not case-sensitive. The wildcard characters * and ? can represent any number of characters and one character respectively; use a tilde, ~, to treat a wildcard character literally. For date comparisons, make sure the source contains real Excel dates, not text that only looks like a date. Regional date formats can be ambiguous, so test criteria against known records in the workbook.

Excel version, platform, and macro safety

The Range.AdvancedFilter method is an Excel VBA feature and is available in desktop Excel, including Microsoft 365 for Windows. Excel for the web can display a workbook containing VBA but cannot run its macros. Excel for Mac supports VBA, though editor access and some keyboard shortcuts differ; use Tools > Macro > Visual Basic Editor if the Windows Alt+F11 shortcut is not available on your Mac keyboard.

Save a working copy before running or modifying a macro. VBA changes cannot be undone with Ctrl+Z, and the example clears existing values in Results before generating the new extract. Save a workbook that needs to retain VBA in a macro-enabled format such as .xlsm, and only enable macros in files you trust.

Frequently Asked Questions

How do I apply AND criteria in Excel Advanced Filter VBA?
Put each required condition in the same criteria row, under its matching source heading. For example, Region = West and Units >=100 belong on one row if both must be true.

How do I apply OR criteria?
Put each alternative on a separate criteria row. If a record can match either Region = West or Region = East, list West and East on separate rows below the Region heading.

Can Advanced Filter copy only selected columns?
Yes. Put only the source field names you want in the destination header range, spelled exactly as in the source. Advanced Filter copies the matching records’ values for those fields.

Why does my VBA Advanced Filter return no results?
First check that criteria headings match source headings, the source range includes its header, and the criteria logic is arranged across rows and columns as intended. Then check that numbers, dates, and text in the source have the types and values your criteria expect.

Can I use Advanced Filter in Excel for the web?
VBA macros cannot run in Excel for the web. Open the workbook in desktop Excel to run the macro; web access alone is not a replacement for executing VBA.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.