To filter a column for several exact values with Excel VBA, pass an array to Criteria1 and set Operator:=xlFilterValues. For example, dataRange.AutoFilter Field:=3, Criteria1:=Array("East", "West"), Operator:=xlFilterValues shows rows whose third field is East or West.
What an AutoFilter array does
Excel’s AutoFilter normally applies one criterion to a field: for example, show only rows where Region equals East. An array lets the filter accept a list of allowed values instead. In this example, East and West are combined as an OR condition: a row is visible if its Region matches either value. This is useful for selecting several departments, product codes, names, or statuses without creating a separate filter operation for each one.
The array is not an output array and it does not copy matching records to another location. It supplies choices to Excel’s built-in column filter, which hides rows that do not match. The underlying data remains in place, and the filter drop-down shows that the field has an active filter.
The key pieces are:
Criteria1receives the array of values to include.Operator:=xlFilterValuestells AutoFilter to treat those values as a selected-value list.Fieldidentifies the filtered column by its position inside the range being filtered.
A working VBA example
Suppose a worksheet named Sales has headers in A1:D1, with Region in column C and records directly below the headers. This macro filters the contiguous table to show East and West. The range must include the header row, and the Region field is the third column within A:D.
Sub FilterRegions()
Dim ws As Worksheet
Dim dataRange As Range
Dim criteria As Variant
Set ws = ThisWorkbook.Worksheets("Sales")
Set dataRange = ws.Range("A1").CurrentRegion
criteria = Array("East", "West")
' Field 3 is column C within the A:D filter range.
dataRange.AutoFilter Field:=3, Criteria1:=criteria, Operator:=xlFilterValues
End Sub
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z; this particular macro changes the filter state rather than deleting records, but keeping a copy is still a sensible precaution. Save the file in a macro-enabled format such as .xlsm if you need to retain its VBA code.
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor, insert the procedure in a standard module, and run it while the workbook is open. Excel for Mac desktop also supports VBA and AutoFilter, although the editor and some other Excel features differ. Excel for the web can display a workbook containing macros but cannot run VBA.
How Field numbers and ranges work
A frequent source of mistakes is treating Field as an absolute worksheet column number. It is relative to the left edge of the range passed to AutoFilter. If the range is A1:D100, Field 1 means A, Field 2 means B, and Field 3 means C. If the range starts at B1, Field 1 means B, not A.
CurrentRegion is convenient when a table is a single, uninterrupted block surrounded by blank rows or columns. It can produce an unexpected range if there is a blank row or column inside the data, or if nearby notes are connected to the block. For predictable results, define the range explicitly or use an Excel Table’s range. For example, a table named SalesTable can be filtered with Worksheets("Sales").ListObjects("SalesTable").Range.AutoFilter, followed by the same Field and criteria arguments. The field number is still relative to that table range.
Make sure the first row of the selected range contains headers. AutoFilter uses that row for the drop-downs; including a title row above the real headers can make the field positions and filter labels confusing.
Build the criteria array dynamically
The values do not have to be typed directly into the procedure. A Variant variable can hold the array returned by VBA’s Array function, as in the example. You can also assign a range of cells to a Variant and pass its values, which is useful when users maintain the permitted choices on a worksheet. For a vertical list in F2:F4, for instance, read that range into a Variant and pass it as Criteria1; check the shape of the resulting array if the list contains only one cell, because a single-cell range is not represented in the same way as a multi-cell range.
For a short, fixed list, an inline array is easiest to read. For a changing list, create the criteria from a validated source range or collection, and remove empty entries before applying the filter. Empty cells accidentally included in the criteria may affect which rows are shown. If the list is generated from user input, validate it and confirm that the values match the column’s underlying data type.
Text comparisons in Excel filters are generally not case-sensitive. An array containing “east” will normally match East as well as east; it is not a reliable way to enforce case-sensitive matching. Values also need to correspond to the data in the filtered field: a trailing space, a different spelling, or a text value that only looks like a number can lead to unexpected results.
Filter more than one field
AutoFilter can retain criteria on multiple fields at once. Apply the array filter to one field, then call AutoFilter again on the same range for another field. Excel combines filters across different columns with AND logic: if Region is East or West and Status is Open, a visible row must meet the Region condition and the Status condition. Within the Region array, East and West remain alternatives.
This differs from putting every desired value into one array. The array applies only to the field identified by that call’s Field argument. To filter a different column, make another call with its own field number and criterion. When automating a workbook with existing filters, use the same data range consistently; changing the range between calls can make the filter setup confusing or disrupt the existing state.
Numbers, blanks, wildcards, and dates
For ordinary text categories, an array of strings is the clearest approach. Numeric fields can also be filtered by selected values, but be careful when a column mixes numbers stored as numbers with number-like text. Those are different underlying values in Excel. If a selected numeric item fails to match, inspect the source cells’ actual values and data types rather than relying on how they appear on screen.
Blank and nonblank selection are often clearer as single criteria than as members of a list: AutoFilter uses Criteria1:="=" to select blanks and Criteria1:="<>" to select nonblank cells. These are criteria expressions, not literal text values. Wildcards such as * and ? are intended for pattern criteria; do not assume that a list of array values behaves like a set of wildcard searches. If the goal is “starts with” or “contains,” use an appropriate wildcard criterion or prepare a helper column.
Date filters deserve special care. Dates are stored as serial numbers, while their displayed format depends on the workbook’s regional settings. A list of date strings can therefore be ambiguous. Excel also has a special grouped-date form that uses Criteria2 with xlFilterValues, where the array contains level-and-date pairs—for example, a month level and a date representing that month. Use that documented date-filter structure for grouped year, month, or day selections, and avoid locale-dependent date text when a workbook will move between regions.
Clear or replace an existing filter
Running the example again with different criteria replaces the selection for that field. It does not remove the filter arrows. To show every row while leaving the AutoFilter controls in place, use ShowAllData when the worksheet is actually filtered:
Sub ClearWorksheetFilter()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales")
' Show all rows only when a filter is currently hiding data.
If ws.FilterMode Then ws.ShowAllData
End Sub
FilterMode indicates whether the worksheet is currently filtering data, and the guard prevents calling ShowAllData when there is no active filtered result. This clears criteria on the sheet’s active AutoFilter; it is not a command to delete rows. Removing filter arrows is a separate action and should not be confused with showing all records.
Common errors and how to fix them
- Wrong column is filtered: Recount Field from the leftmost column in the exact range passed to AutoFilter, not from worksheet column A.
- Nothing matches: Check spelling, extra spaces, data types, and whether the array contains the values as they exist in the field. Also verify that the filter range includes the records.
- Run-time error or unexpected range: Confirm the worksheet name, that the range includes headers, and that
CurrentRegionhas not been split by blank rows or columns. - Existing filters behave oddly: Keep calls on the same range and consider what criteria are already active on other fields. A filter on another field still restricts the visible rows.
- Macro is unavailable: Check that macros are enabled under your organization’s security rules and that the workbook is saved in a macro-capable format. Do not enable macros in an untrusted file.
AutoFilter hides nonmatching rows; it does not erase them. If a later operation copies only visible cells, make sure the copy operation is intentionally limited to visible results. Conversely, if the requirement is to produce a separate, formula-driven result that updates in cells, a row-hiding filter may not be the right tool.
When to use AutoFilter instead of alternatives
Use VBA AutoFilter when the desired result is an interactive filtered table, or when a macro needs to show a chosen subset quickly. It preserves the source data and uses Excel’s familiar filter controls. A worksheet’s filter drop-down can also apply a selection manually when automation is unnecessary.
If you need a separate extracted list that recalculates as inputs change, the FILTER worksheet function may be more suitable in Microsoft 365 or Excel 2021 and later. Unlike AutoFilter, it returns results into cells and requires an output area where the results can spill. For repeatable data transformation, Power Query is another option; it creates a refreshable query result rather than merely hiding rows. Choose based on whether you need an interactive view, a formula result, or a refreshable transformed dataset.
Frequently Asked Questions
How do I filter multiple values in Excel VBA?
Pass a VBA array to Criteria1 and use Operator:=xlFilterValues. For example, on a range whose third field is Region, use an array containing East and West with Field set to 3.
Can I use a cell range as the AutoFilter criteria array?
Yes. Read the cells into a Variant and pass that value as Criteria1. Check for blank cells and account for the different array shape returned when the source range contains just one cell.
Does AutoFilter array mean the rows are copied?
No. AutoFilter hides rows that do not match; it leaves the records in their original positions. Use a formula, a copy operation, or a data transformation tool if you need a separate result.
Why does Field:=3 filter the wrong column?
Field is counted from the first column in the AutoFilter range. In a range starting at B, Field 3 refers to D, not worksheet column C.
Can I filter several columns at the same time?
Yes. Apply criteria to each field on the same range. Values within one field’s array act as alternatives, while criteria across separate fields are both required for a row to remain visible.
Does VBA AutoFilter work in Excel for the web?
No. Excel for the web can work with many workbook contents, but it does not run VBA macros. Use Excel desktop for Windows or Mac to run this code.
Related
- Excel Solver Not Installed? How to Enable It
- Excel Solver Not Responding? Fixes and Causes
- Excel Solver: Whole Numbers, Integers, and Constraints
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Offset: Move a Range by Rows and Columns in a MacroIn an Excel VBA macro, use the Offset property to refer to a cell or range a chosen…
- How to Create a Word Template With MacrosTo create a Word template that contains a macro, start with a blank document or an existing file,…
- Excel VBA: Open a Workbook Without Updating LinksTo open an Excel workbook with VBA and skip updating its external workbook links, pass UpdateLinks:=0 to Workbooks.Open.…
- Excel Power Pivot VBA: What You Can AutomateYes—VBA can automate useful parts of Excel Power Pivot, especially refreshing workbook data and updating reports built from…
- VBA Excel: Copy a Range of CellsTo copy a range of cells in VBA, use the range’s Copy method and specify the top-left destination…
- VBA Sort Sheets Alphabetically: Macro and StepsYes. In Excel for Microsoft 365 on Windows, a short VBA macro can sort a workbook’s sheet tabs…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.