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:
xlFilterCopycopies matching rows to the range named byCopyToRange. This is usually preferable for a report because it leaves the source list intact.xlFilterInPlacehides 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
- Excel SUM with Multiple Criteria: How to Do It
- Excel: Subtract the Same Value From Multiple Cells
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Exit Function: When and How to Use It SafelyIn Excel VBA, Exit Function stops the current function immediately and returns control to the statement that called…
- Excel VBA: Open a File Based on a Cell Value SafelyTo open a workbook whose path is stored in a cell, read that cell into a VBA string…
- Excel VBA: How to Delete a Worksheet ButtonTo delete a button from an Excel worksheet, select the button itself and press Delete; for a Form…
- Excel VBA: Convert a Variant to a StringIn Excel VBA, use CStr to convert a Variant value to a String: for example, textValue = CStr(value).…
- VBA Dictionary Index: Access Items by PositionA VBA Scripting.Dictionary has no built-in numeric index for its entries: you retrieve an item by its unique…
- Excel VBA MsgBox New Line: Add Line Breaks CorrectlyTo put a new line in an Excel VBA message box, join the text with vbCrLf: MsgBox "First…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.