Excel VBA AutoFilter Multiple Criteria: A Practical Guide

VBA & macros · Updated November 21, 2026

To filter a range with Excel VBA using multiple allowed values in one column, pass an array to Criteria1 and set Operator:=xlFilterValues. For example, Criteria1:=Array("East", "West") keeps rows matching either region; filters applied to different columns combine to narrow the results further.

How AutoFilter criteria work

AutoFilter hides rows that do not meet the criteria; it does not delete or rearrange the underlying records. In VBA, the Range.AutoFilter method applies a filter to a range that includes a header row. Its Field argument identifies the column to filter, while Criteria1, Operator, and optionally Criteria2 describe what to keep.

The field number is relative to the range supplied to AutoFilter, not necessarily the worksheet column letter. If the range begins in column B, Field:=1 refers to column B. Include the header row in the range; otherwise, Excel may treat the first record as a header and exclude it from the results.

There are two common meanings of “multiple criteria”: several alternative values in one field, or conditions across multiple fields. The syntax differs. A list of values for one field is usually an OR condition, while filters on separate fields usually narrow the result as an AND condition.

Use a list of alternative values in one column

For a field that should match one of several exact values, use an array with xlFilterValues. This is useful for choosing several regions, departments, or status labels without writing a separate filter for each one. The following macro assumes the headers are in row 1 and the data occupies A1:D100, with Region in column A and Sales in column D.

Sub FilterRegionsAndSales()
    Dim ws As Worksheet
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' Remove any existing sheet-level AutoFilter before applying this range.
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    ' Keep rows where Region is East OR West.
    dataRange.AutoFilter Field:=1, _
        Criteria1:=Array("East", "West"), _
        Operator:=xlFilterValues

    ' Also keep rows where Sales is at least 1000.
    dataRange.AutoFilter Field:=4, Criteria1:=">=1000"
End Sub

The result is the intersection of the conditions: a row must have Region equal to East or West and Sales of at least 1000. Replace the worksheet name, range, field numbers, and values to fit your sheet. Save a copy of the workbook before running any macro: VBA changes cannot be reversed with Ctrl+Z. The reset line removes an existing sheet-level AutoFilter, so use it only when that is appropriate for your worksheet.

Combine two conditions on the same field

For a range between two numeric limits, set Operator:=xlAnd and supply both criteria. With sales values in the fourth field of A1:D100, this keeps values from 1,000 up to, but not including, 5,000:

Sub FilterSalesRange()
    Dim ws As Worksheet
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' Start with a clean sheet-level filter.
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    ' Both conditions apply to the Sales field.
    dataRange.AutoFilter Field:=4, _
        Criteria1:=">=1000", _
        Operator:=xlAnd, _
        Criteria2:="<5000"
End Sub

For two alternatives on one field, use xlOr with Criteria1 and Criteria2, such as one condition for values below 10 and another for values above 100. That approach supports two comparison conditions. When the alternatives are a longer list of exact labels, an array with xlFilterValues is clearer and easier to maintain.

Filter across multiple columns

Apply AutoFilter once for each field you want to constrain. For example, you can filter Region to East or West, then filter Status to Open. Excel displays only rows satisfying both field filters, while the two region values remain alternatives within the Region field. Use the same range and its header row for every call, and use a different Field number for each column.

A common mistake is to call AutoFilter twice on the same field expecting the conditions to accumulate. A later call for that field replaces its previous criteria. Put two same-field comparisons into one call using xlAnd or xlOr, or pass a value array when the goal is to select a list. Calls on different fields, by contrast, maintain the filters together.

Text, wildcard, blank, and comparison criteria

Criteria are usually written as strings, including comparison operators. Common examples include "=Open", ">=100", and "<>Closed". For text matching, Excel AutoFilter supports wildcards: an asterisk represents any number of characters, and a question mark represents one character. For instance, "North*" matches text beginning with North. Prefix a wildcard with a tilde to search for it literally, as in "~*" for a value beginning with an asterisk.

To filter blank or nonblank cells in a field, the usual criteria are "=" and "<>", respectively. Be mindful that cells that look blank may contain spaces or formulas returning an empty string, so check the source data if the results seem surprising. For dates, make sure the column contains real Excel date values rather than inconsistent text. Date criteria can be affected by regional date formats; test the chosen criteria against a small sample before applying them to an important report.

Ranges, tables, and clearing filters

A fixed address such as A1:D100 is simple, but it will not include records added below row 100. For regularly growing data, an Excel Table is often more convenient: tables expand as records are added and provide structured headers. In VBA, a table is represented by a ListObject; its range can be used for filtering, or its AutoFilter object can be managed directly. Check that your code targets the intended table when a worksheet contains more than one.

Excel exposes two related worksheet properties: AutoFilterMode indicates whether filter dropdown arrows are displayed, while FilterMode indicates whether rows are currently filtered. To show all rows without removing the dropdowns, use ShowAllData when the sheet is in filter mode. To remove the arrows from an ordinary range filter, set AutoFilterMode to False. These actions are different, so choose based on whether you want to clear criteria or remove the filter controls as well.

Common errors and how to fix them

  • Wrong field number: Count from the left edge of the filtered range, not from worksheet column A. Confirm that the supplied range starts where you expect.
  • Header omitted: Include the header row in the AutoFilter range. Use a consistent header and data layout so field numbers remain predictable.
  • Values do not match: Check spelling, trailing spaces, and whether numbers or dates are stored as text. Array criteria are most reliable when their values match the cells exactly.
  • Existing filters interfere: A filter may already be active on another range or table. Clear or target the correct filter deliberately rather than removing sheet filters without checking.
  • No visible change: The criteria may match no rows, or the matching rows may already be the only visible ones. Test with a value you know exists and verify the field index.

If the source range is dynamic, determine its last row carefully or use a table rather than relying on an arbitrary fixed endpoint. Avoid including unrelated columns in a filter range: all fields should belong to the same header-and-record block. When criteria depend on user input, validate the input before using it; unexpected spaces or blank entries can make a filter appear unreliable.

Windows and Mac considerations

This guidance targets Excel for Microsoft 365 on Windows. The VBA editor and macro security controls differ between Windows and Mac, and Excel for Mac has a documented limitation for the Range.AutoFilter method; Microsoft notes that similar methods on a Selection and on a ListObject are supported. If a macro must run on Mac, test the exact object method and workbook structure in the Mac version you use, and consider filtering a table through its ListObject rather than assuming a Windows range call will work unchanged. Excel for the web does not run VBA macros.

On Windows, open the VBA editor with Alt+F11. On Mac, the corresponding shortcut is commonly Option+F11, though keyboard settings and hardware can affect function-key behavior; the Visual Basic Editor is also available from the Developer tab. Macros may require saving the workbook in a macro-enabled format such as .xlsm and enabling macros according to your organization’s security policy.

Frequently Asked Questions

How do I filter multiple values in one column with VBA?

Use Criteria1:=Array("Value1", "Value2") with Operator:=xlFilterValues. Replace the sample values with entries that match the cells in that field. This selects rows matching any listed value.

Does AutoFilter use AND or OR for multiple criteria?

For two conditions on one field, specify xlAnd or xlOr explicitly. A value array with xlFilterValues represents alternatives. Filters on different fields are combined so a row must satisfy each active field filter.

Can I use more than two criteria in one AutoFilter call?

For a list of exact values, use an array with xlFilterValues, which is the usual solution for several alternatives. The standard Criteria1 and Criteria2 arguments provide two comparison criteria, combined with an operator such as xlAnd or xlOr.

How do I remove the filter but keep all rows visible?

If a filter is active, ShowAllData displays all rows while leaving the dropdown arrows in place. Setting AutoFilterMode to False removes the arrows too. Check the sheet’s filter state before calling either method.

Why does my AutoFilter macro work on Windows but not Mac?

Some AutoFilter object methods differ in Mac support. In particular, Microsoft documents a limitation for Range.AutoFilter on Mac and identifies Selection and ListObject alternatives. Test the specific method in the target Excel version, especially when the workbook must run on both platforms.

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.