Excel VBA AutoFilter with an Array: A Practical Guide

VBA & macros · Updated November 21, 2026

Yes. In Excel VBA, pass a one-dimensional array of the values you want to keep to the range’s AutoFilter method, using Operator:=xlFilterValues. For example, dataRange.AutoFilter Field:=3, Criteria1:=Array("East", "West"), Operator:=xlFilterValues shows rows whose third field is East or West.

What the array filter does

An AutoFilter normally applies a condition to one column of a list or table. With an array of criteria, you can ask Excel to display records matching any one of several listed values in that field. This is useful when the items you want do not form a simple range, such as several departments, product codes, or regions selected from a longer list.

The array is an OR-style list: a row remains visible when its field matches one of the listed entries. It does not mean that a row must match every value in the array; a single cell cannot, for example, equal both East and West at once. For different conditions on different columns, apply AutoFilter to each field in turn.

In the method call, Criteria1 receives the array and Operator:=xlFilterValues tells Excel to treat the criteria as a list of values. The Field argument identifies the column by its position within the filtered range, not by its worksheet column letter. Those details account for many errors in otherwise plausible macros.

A complete example for a worksheet range

This example assumes the Orders worksheet has a header row in row 1, a continuous data block beginning in A1, and the Region field is the third column of that block. It detects the block with CurrentRegion, then filters for East and West. Adjust the sheet name and field number to match the actual layout.

Sub FilterOrdersByRegion()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim regions As Variant

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion
    regions = Array("East", "West")

    ' Show all records first if this range is already filtered.
    If ws.FilterMode Then ws.ShowAllData

    ' Field 3 means the third column within dataRange.
    dataRange.AutoFilter Field:=3, Criteria1:=regions, Operator:=xlFilterValues
End Sub

Save a copy of the workbook before running a macro. VBA changes are not undone with Ctrl+Z, so a backup is the reliable way to recover the previous state. On Windows, open the VBA editor with Alt+F11; on a Mac, Option+F11 is commonly used, though some keyboards require Fn as well. Excel for the web can display a workbook containing VBA but cannot run VBA macros.

How Field and the filtered range work

The range supplied to AutoFilter defines both the header row and the columns that the field numbers refer to. If the filtered range is A1:F200, then Field:=1 means column A, Field:=3 means column C, and Field:=6 means column F. If the range instead starts in column B, Field 1 means B, not A.

Include the header row in the range. AutoFilter uses it to identify the column headings and applies the condition to the rows beneath it. A missing header, an incorrect starting cell, or a field number larger than the range’s column count can lead to errors or a filter on the wrong column.

CurrentRegion is convenient when the data forms one uninterrupted rectangle around A1. It can stop at blank rows or columns, and it may include adjacent material if that material touches the data. For a predictable report, a fixed range or an Excel Table is often safer. A table’s data range can be referenced through its DataBodyRange, while its header row is represented separately; make sure the range passed to AutoFilter includes the headers when using a range-based call.

Using a variable array

The criteria do not have to be typed directly into the AutoFilter call. You can build an array earlier in the macro, which is useful when the values come from user choices, another worksheet, or the output of preceding VBA logic. The array should contain the actual values to match, not a comma-delimited string that looks like an array.

Sub FilterForSelectedStatuses()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim statuses As Variant

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion
    statuses = Array("Open", "On Hold", "Backorder")

    If ws.FilterMode Then ws.ShowAllData
    dataRange.AutoFilter Field:=5, Criteria1:=statuses, Operator:=xlFilterValues
End Sub

Here, the assumed layout has Status in the fifth column of the range beginning at A1. The values in the array must correspond to the values in that column. For example, an extra space in a source value such as “Open ” can prevent it from matching “Open.” Check for inconsistent spelling, leading or trailing spaces, and differences between numeric values and numbers stored as text when expected rows disappear.

A VBA Array function creates a zero-based array by default, but that does not mean the AutoFilter field numbering becomes zero-based. The array is just the list of criteria; Field still starts at 1 relative to the filtered range.

Filtering more than one column

To combine a list of values in one field with a condition on another field, call AutoFilter once for each field on the same range. Excel keeps the conditions together, so the resulting rows must satisfy the filter on each column. For example, filter Region for East or West, then filter Status for Open. That gives East-or-West rows that are also Open; it does not give every East, West, or Open row regardless of the other field.

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

    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion

    If ws.FilterMode Then ws.ShowAllData
    dataRange.AutoFilter Field:=3, Criteria1:=Array("East", "West"), Operator:=xlFilterValues
    dataRange.AutoFilter Field:=5, Criteria1:="Open"
End Sub

In this example, the data range is assumed to start in column A, with Region in its third field and Status in its fifth. If you need a different logical arrangement—such as an OR across conditions in different columns—sequential AutoFilter calls may not express it directly. Consider a helper column that calculates a TRUE/FALSE condition, or use a more suitable filtering approach for the required logic.

Choosing the right AutoFilter operator

For a list of discrete values, xlFilterValues is the relevant operator. It is not interchangeable with every AutoFilter operator. For two conditions on the same field, such as values greater than a threshold and less than another threshold, use the comparison criteria with xlAnd. For alternatives expressed as two criteria, xlOr can be appropriate. A list of several exact choices is clearer as an array with xlFilterValues.

  • Several selected items: Use an array and xlFilterValues.
  • A range of numbers or dates: Use comparison criteria with xlAnd where appropriate, or another method if the condition is more complex.
  • Text patterns: AutoFilter supports wildcard criteria such as an asterisk for a sequence of characters and a question mark for one character; a tilde escapes a wildcard character. Use pattern criteria deliberately rather than assuming a value-list array is a wildcard search.
  • More complicated logic: A helper column, Advanced Filter, or a VBA loop that constructs a separate result may be easier to maintain.

Keep the operator aligned with the type of criteria. Supplying an array without xlFilterValues can cause Excel to interpret the arguments differently than intended. If a filter is not behaving as expected, first reduce it to one field and two known values, then add the rest of the logic.

Clearing filters and retaining dropdown arrows

Clearing the active criteria is different from removing the filter controls. If the range is filtered, ShowAllData displays all records again but leaves the AutoFilter dropdown arrows available. The sample macros use If ws.FilterMode Then ws.ShowAllData to avoid calling that method when there is no active filter to clear.

Setting ws.AutoFilterMode = False removes the worksheet’s AutoFilter arrows. That may be appropriate when a macro is meant to remove the controls, but it is not necessary just to show every row. Avoid turning AutoFilterMode off as a routine reset if users expect the dropdowns to remain on the sheet. Also be careful when a worksheet contains more than one filtered list: confirm that the range and filter you intend to change are the ones being addressed.

Common problems and practical checks

  • Wrong field number: Count from the first column in the AutoFilter range, starting at 1. Do not use the worksheet’s absolute column number unless the range begins in column A.
  • Wrong data range: Confirm that the range includes the headers and all intended records. A blank row can divide a CurrentRegion into separate blocks.
  • Criteria do not match: Compare the array entries with the source values, including spaces and text-versus-number differences. A spelling difference is enough to exclude a record.
  • Existing filters hide results: Clear prior criteria when the macro needs a clean starting state. If the code should preserve other filters, do not clear all of them without considering that requirement.
  • One criterion is passed instead of a list: Array("East,West") contains one item with a comma, not two values. Use Array("East", "West").
  • Date values behave differently: Date filtering can be sensitive to Excel’s date representation, regional settings, and grouped date levels. Avoid assuming a text date will be interpreted identically on every computer; test with the workbook’s date data and locale.
  • No visible rows after filtering: The requested values may genuinely be absent, or the wrong field/range may be in use. Check the unfiltered source and try one known value before troubleshooting the full list.

For a macro used by other people, validate the criteria before applying them. For example, if the list of requested values is assembled from a separate sheet, check that it is not empty and that its entries are not blank. A deliberate message for an empty selection is more helpful than silently leaving users unsure why no rows are displayed.

Alternatives when an array filter is not enough

AutoFilter is a good fit when the goal is to hide nonmatching rows in place and let users inspect the original list. An Excel Table also provides built-in filter dropdowns and expands as records are added, making it a natural choice for recurring data-entry and review tasks.

If the desired result is a separate extracted list rather than hidden rows, a helper column or Advanced Filter may be more suitable. In Microsoft 365 and Excel 2021 or later, the worksheet FILTER function can return matching rows as a spill range, but that is a formula-based result rather than a VBA AutoFilter operation. In Excel for the web, worksheet filtering and formulas are available, but VBA itself cannot be run there. Choose based on whether users need to filter the source list, create a separate output, or automate a repeatable desktop workflow.

Frequently Asked Questions

Can I use multiple values in Criteria1?
Yes. Pass a one-dimensional array of the desired values as Criteria1 and specify Operator:=xlFilterValues. The values act as alternatives for that field.

Does Field mean the worksheet column number?
No. Field is the column’s position inside the AutoFilter range, counting from 1. If the range starts in column C, then Field 1 refers to C.

Can I filter two columns with different arrays?
Yes. Apply AutoFilter to the same range once for each field, using a separate criteria array for each list. Excel combines those field filters so records must meet both fields’ conditions.

Why does Array(“East,West”) not match both values?
That expression creates one array item containing the text East,West. Separate criteria require separate elements: Array("East", "West").

Can Excel for Mac or Excel for the web run this VBA?
Excel for Mac supports VBA, though keyboard shortcuts and some workbook features can differ from Windows; Option+F11 may open the editor, sometimes with the Fn key. Excel for the web does not execute VBA macros, so run the macro in the desktop application.

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.