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
xlAndwhere 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. UseArray("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
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
- Excel Text to Columns: Advanced Options and Uses
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Select Case: Handle Button ChoicesUse VBA’s MsgBox function to display buttons, store the button the user clicks, and branch on that result…
- Excel VBA Cross Product: Create Cartesian PairsExcel has no built-in worksheet function named CROSSPRODUCT for pairing two lists, but you can create a VBA…
- Excel VBA Text Function: Format Values and CellsIn Excel VBA, use the Format function—usually Format$—to turn a number or date into a formatted string; Excel’s…
- Excel VBA Set Cell Color: ColorIndex, RGB, and MoreTo set a cell’s fill color with VBA, assign a color to its Interior.Color property—for example, use RGB(255,…
- Macro Recorder Compatibility Across Excel VersionsExcel’s macro recorder creates VBA macros that work in desktop Excel for Windows and Mac, but compatibility is…
- Excel VBA UserForm Tutorial: Build a Data Entry FormTo build an Excel VBA UserForm, open the Visual Basic Editor, insert a UserForm, add controls such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.