VBA AutoFilter Criteria Array: Syntax and Examples

VBA & macros · Updated November 18, 2026

To filter a field for several allowed values in VBA, pass a Variant array to Criteria1 and set Operator:=xlFilterValues. For example, dataRange.AutoFilter Field:=3, Criteria1:=Array("North", "West"), Operator:=xlFilterValues shows rows whose third field is North or West.

What a criteria array does

An AutoFilter normally applies a condition to one field in a list or table: for example, show only rows where Region equals North. A criteria array lets you select multiple discrete values for that same field, much like checking several items in Excel’s filter dropdown. The values are alternatives, so the filter keeps a row when its field matches any item in the array.

This is useful when the values do not form a simple range or rule. You might show several departments, selected product codes, or a short list of statuses while hiding all other values. It is different from an AND condition such as “greater than 10 and less than 20,” which uses comparison criteria and an operator such as xlAnd.

The array is not a worksheet formula and does not need to be placed in cells. VBA creates it in memory and passes it to the AutoFilter method. In this context, Array returns a Variant containing the items, which is the form AutoFilter expects for a multi-item value filter.

The syntax and the role of xlFilterValues

The general pattern is someRange.AutoFilter Field:=fieldNumber, Criteria1:=values, Operator:=xlFilterValues. someRange is the full filtered range, including its header row; fieldNumber identifies a column within that range; and values is the array of items to retain.

  • Criteria1 receives the array, such as Array("Open", "Pending").
  • Operator:=xlFilterValues tells Excel to interpret the criteria as a list of selected values.
  • Field is a position inside the filtered range, not necessarily the worksheet’s column number.

For instance, if the filtered range starts in column A, field 3 is column C. If the range starts in column D, field 3 is column F. This relative numbering is a frequent source of errors: use the column’s position in the range rather than its letter or absolute worksheet index.

A complete example for Microsoft 365 on Windows

Suppose a worksheet named Data has a contiguous table-like block with headers in row 1. Its third column is Region, and the data includes North and West among other values. This macro filters that field to those two regions:

Sub FilterSelectedRegions()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim criteria As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion
    criteria = Array("North", "West")

    ' Keep rows matching either selected region.
    dataRange.AutoFilter Field:=3, Criteria1:=criteria, _
        Operator:=xlFilterValues
End Sub

CurrentRegion takes the contiguous block around A1, so this assumes there are no fully blank rows or columns breaking up the data. It also assumes row 1 contains headers and the Region field is the third column in that block. If those assumptions do not fit the worksheet, set dataRange explicitly, such as ws.Range("A1:D250"), and adjust the field number accordingly.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, put the code in a standard module in the Visual Basic Editor and run the procedure with the workbook’s data sheet arranged as expected. Macros do not run in Excel for the web.

Building the array from worksheet cells

You do not have to hard-code every choice. A short list on a control sheet can supply the criteria, which is convenient when users need to change the selection without editing VBA. For example, suppose the permitted Region values are in Control!A2:A4, with no blank cells. The following macro reads them into a Variant array and applies the filter:

Sub FilterRegionsFromCells()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim criteria As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion
    criteria = ThisWorkbook.Worksheets("Control").Range("A2:A4").Value

    ' Apply the values in Control!A2:A4 to field 3.
    dataRange.AutoFilter Field:=3, Criteria1:=criteria, _
        Operator:=xlFilterValues
End Sub

A multi-cell range’s Value is a two-dimensional Variant array. AutoFilter can accept that array for a list filter, but it should contain the intended items in a single column or row and should not include unwanted blanks. A one-cell range behaves differently: its value is a scalar rather than a two-dimensional array. If the list may contain only one item, you can handle that case separately or construct a one-item array deliberately, then check the result on a copy of the workbook.

For a variable-length list, determine the last used cell in the criteria column before reading the range. Also decide what an empty list should mean. Passing an empty or malformed criteria source is not a reliable way to clear a filter; explicitly handle the empty-list case instead of letting the macro proceed with missing values.

Choosing the right range and field

The range passed to AutoFilter defines both the rows being filtered and the numbering of its fields. It should include the column headers and the records you want Excel to filter. If the range omits the header row, Excel may treat the first record as a header, which can make the results confusing. If it omits part of the data, those rows will not be included in the filter.

For a stable report, an Excel Table can be easier to maintain than a fixed address because the table expands when records are added. A table also provides a clear boundary for the data. If you use a normal range, update its address or its method of finding the last row when the dataset grows. Avoid assuming that CurrentRegion always identifies the correct data: a blank row within the data can split the region, while adjacent notes can cause it to include extra columns.

  • Confirm the header is the first row of the AutoFilter range.
  • Count fields from the left edge of that range, starting at 1.
  • Check that the target field contains the same kind of values as the criteria.
  • Make sure the range includes every record that should be affected.

Matching text, numbers, and dates

Use criteria that correspond to the actual values in the column. If a column contains the number 12, passing the text string "12" may not behave as intended in every data setup; use a numeric value when the field is numeric. Similarly, dates stored as real Excel dates are different from text that merely looks like a date. Mixed data types, imported values, and inconsistent whitespace can all explain why a value that appears identical on screen is not matching as expected.

Text comparison in Excel filters is generally not case-sensitive, so an array of differently capitalized versions is not a dependable way to distinguish case. Leading or trailing spaces, however, can make values genuinely different. If the source data is untidy, clean or normalize it first, or use a helper column to produce consistent values before filtering.

Date filters have an additional wrinkle. A list of exact date values can be used as a value filter when that is the intended match, but Excel also has grouped date-filter criteria for selecting years, months, or individual dates. Those grouped date criteria use Criteria2 in a special level-and-date structure; they are not simply interchangeable with the ordinary Criteria1:=Array("North", "West") pattern. Be especially careful with dates written as text because their interpretation can depend on regional date settings.

Common mistakes and how to fix them

If the macro runs but returns no visible rows, first check the field number and the exact data type and spelling of the criteria. Then verify that the range includes the expected records. A wrong field number is especially easy to make when the range begins somewhere other than column A.

  • Missing operator: An array used for selected values should be paired with Operator:=xlFilterValues. Without it, Excel may interpret the criteria differently or fail to apply the intended list filter.
  • Wrong field index: Count from the first column in the filtered range, not from worksheet column A.
  • Header excluded: Include the header row so Excel knows the field names and data boundary.
  • Values do not match: Check spelling, spaces, numeric-versus-text values, and whether the displayed text is actually the stored value.
  • Existing filters or protected sheet: A previous filter can affect what is visible, and worksheet protection can prevent filter changes unless filtering is allowed.

To show all records again without removing the filter arrows, use Excel’s Clear command for the active filter or call ShowAllData when the worksheet is currently filtered. Check the filter state before calling it, because ShowAllData can raise an error if there is no active filtered result. Clearing filters is different from turning off AutoFilter entirely.

Range.AutoFilter on Mac and Excel for the web

The code examples above target Excel for Microsoft 365 on Windows. Microsoft documents the Range.AutoFilter method as unsupported on Excel for Mac; related AutoFilter methods on a Selection or a ListObject may be available, so Mac users should adapt the object reference and verify the method in their current Excel version. The filter concept and criteria-array syntax are the same, but do not assume that a Windows Range example will run unchanged on Mac.

Excel for the web can display and use worksheet filters, but it does not run VBA macros. To automate the workbook in a browser, consider Office Scripts if the required operations are supported there, or run the VBA in desktop Excel. Office Scripts use TypeScript rather than VBA, so VBA code cannot simply be pasted into an Office Script.

Frequently Asked Questions

Can Criteria1 use Array with more than two values?
Yes. Supply the set of values you want to retain in the array and use Operator:=xlFilterValues. For example, the criteria can include several statuses, provided the items match the values in the filtered field.

Does AutoFilter use AND or OR for the array items?
The values in a criteria array act as alternatives: a row is kept if its field matches any listed value. That is OR logic within one field. Filtering another field at the same time adds a separate condition, typically narrowing the results further.

Can I use a worksheet range directly as Criteria1?
Yes, a one-dimensional list supplied as a Variant array can be used. A multi-cell range’s Value is two-dimensional, so keep the source list in one row or column and account for the possibility of a one-cell list returning a scalar.

How do I filter for blank cells?
For a blank-only filter, use the AutoFilter blank criterion Criteria1:="=" rather than assuming the dropdown label (Blanks) is the stored cell value. Test the criterion against the workbook’s data, especially if cells contain formulas returning empty text, which are not always equivalent to truly empty cells.

Why does Field:=3 filter the wrong column?
Field is relative to the left edge of the range passed to AutoFilter. If the range starts in column C, field 3 refers to column E, not worksheet column C.

Can I run this VBA in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel to run the macro, or use a separate automation approach such as Office Scripts where appropriate.

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.