In Excel VBA, the basic AutoFilter syntax is Range.AutoFilter Field:=columnNumber, Criteria1:=value. The range should include the list’s header row, and Field is the column’s position within that range—not its worksheet column number.
The AutoFilter method and its arguments
AutoFilter applies a filter to a rectangular range of data. Its optional arguments let you specify the column to filter, one or more criteria, how criteria should be combined, and whether the filter button is visible. In full, the method is commonly written as:
expression.AutoFilter(Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)
In VBA, named arguments make calls easier to read and help prevent mistakes when you omit optional arguments. For example, Field:=2 identifies the second column of the filtered range, while Criteria1:="West" supplies the value to match. You can omit arguments you do not need.
Field: The one-based column number within the filter range. The first column is 1.Criteria1: The primary criterion, such as a text value, a comparison string, or an array of values.Operator: A constant that combines criteria or specifies a filter type, such asxlAndorxlFilterValues.Criteria2: A second criterion, used with some operators.SubField: The data type field to filter in a linked data type cell, when applicable.VisibleDropDown: A Boolean that controls whether the filter arrow is shown for the specified field.
Most ordinary list filters need only Field and Criteria1. The arguments are optional in the method definition, but a criterion without a field does not tell Excel which column to filter. Use the actual range containing the list rather than assuming that Excel will always infer the intended table.
How Field numbers relate to worksheet columns
Field counts from the left edge of the range passed to AutoFilter. If the data occupies columns B through E, then B is Field 1, C is Field 2, D is Field 3, and E is Field 4. It is not correct to use Field:=3 merely because the target column is C on the worksheet.
For example, suppose A1:D100 contains headers in row 1, with Region in column B. The following call filters the second field in that four-column list:
Range("A1:D100").AutoFilter Field:=2, Criteria1:="West"
Including the header row matters: Excel uses it to label the filter buttons and determine the fields. If you pass a range that starts at A2, the first data row may be treated as the header instead. A range may include more than one worksheet column outside the data, but those columns then affect the field numbering, so define the list bounds deliberately.
Writing criteria for text, numbers, and blanks
For a simple exact text match, provide the text as Criteria1. For numeric or date comparisons, provide a comparison operator and value as a string, such as ">=1000". AutoFilter criteria are not worksheet formulas; they are values or comparison expressions interpreted by Excel’s filter feature.
- Exact text:
Criteria1:="West" - Numbers at least 1,000:
Criteria1:=">=1000" - Nonblank cells:
Criteria1:="<>" - Blank cells:
Criteria1:="=" - Text beginning with North:
Criteria1:="North*"
The wildcard * stands for any number of characters, and ? stands for one character. Use a tilde to search for a literal wildcard character rather than treating it as a pattern character. For instance, a criterion containing ~* looks for an asterisk in the text. A criterion such as "<>West" excludes West; it does not mean “not equal to a cell reference.”
When filtering dates, construct comparisons using real date values rather than relying on a date string that could be interpreted differently under another regional setting. The example below uses DateSerial, which creates a VBA date from year, month, and day numbers. For dates from January 1, 2025, up to but not including February 1, 2025, use two criteria joined with xlAnd.
Using two criteria and selecting multiple values
Use Operator:=xlAnd when a record must satisfy both criteria, or Operator:=xlOr when either condition is sufficient. The following example keeps amounts from 1,000 through 4,999 in the third field:
Range("A1:D100").AutoFilter Field:=3, _
Criteria1:=">=1000", Operator:=xlAnd, Criteria2:="<5000"
The upper comparison is exclusive, so 5,000 is not included. For a date interval, the same approach can compare against the first day of the interval and the first day after it. Assuming the dates are in the second field, this filters January 2025:
Range("A1:D100").AutoFilter Field:=2, _
Criteria1:=">=" & CLng(DateSerial(2025, 1, 1)), _
Operator:=xlAnd, _
Criteria2:="<" & CLng(DateSerial(2025, 2, 1))
For a choice among several exact values in one field, pass an array and use xlFilterValues. Here the second field shows rows whose value is East or West:
Range("A1:D100").AutoFilter Field:=2, _
Criteria1:=Array("East", "West"), Operator:=xlFilterValues
These are alternatives for filtering one field. To filter more than one column at once, apply AutoFilter to each field in the same list range; Excel combines the field filters so that a row must meet the active conditions across the columns.
A complete macro example with a changing data range
A hard-coded last row is fine for a fixed report, but a growing list often needs a range calculated at run time. This example assumes headers are in row 1, the data starts in column A, and Region is the second field. It finds the final used row in column A and filters Region to West.
Save a copy of the workbook before running a macro. Macro changes cannot be undone with Ctrl+Z, even if the filter itself can later be cleared.
Sub FilterWestRegion()
Dim ws As Worksheet
Dim lastRow As Long
' Use the worksheet that contains the list.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last nonempty cell in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Include the header row and filter the second field.
If lastRow >= 2 Then
ws.Range("A1:D" & lastRow).AutoFilter _
Field:=2, Criteria1:="West"
End If
End Sub
The range in this example is A:D because the assumed list has four columns. Adjust its right edge if the list is wider or narrower. Finding the last row in column A is reliable only when that column is populated for every data record. If records can have blanks in column A, find the last row using a column that is always populated, or use an Excel Table, which expands with new rows.
Filtering Excel Tables and managing active filters
For a formatted Excel Table, VBA exposes the table through a ListObject. A table can be more convenient than manually building a range because its data range adjusts as rows are added. The following example assumes a table named SalesTable and filters its second field:
Sub FilterSalesTable()
Dim ws As Worksheet
Dim sales As ListObject
' Set the worksheet and table that contain the list.
Set ws = ThisWorkbook.Worksheets("Data")
Set sales = ws.ListObjects("SalesTable")
' Filter the second table column to show West.
sales.Range.AutoFilter Field:=2, Criteria1:="West"
End Sub
Replace the worksheet and table names with the names in your workbook. A table’s first field is its first table column, even if the table begins in worksheet column C or another position. Table names appear on the Table Design tab when the table is selected.
To reveal all rows while leaving the filter controls available, check whether the worksheet is currently filtered before calling ShowAllData. To remove the filter arrows as well, set AutoFilterMode to False. These are different actions: showing all data clears the current filtering, while turning off AutoFilter removes the buttons.
Sub ClearWorksheetFilter()
Dim ws As Worksheet
' Set the worksheet that contains the filtered list.
Set ws = ThisWorkbook.Worksheets("Data")
' Show all rows if a filter is currently hiding some.
If ws.FilterMode Then ws.ShowAllData
' Remove the worksheet's AutoFilter arrows, if present.
If ws.AutoFilterMode Then ws.AutoFilterMode = False
End Sub
For a table, you may prefer to clear its filter while keeping the table and its dropdown controls. Avoid switching off worksheet AutoFilter mode unless removing the arrows is actually the goal.
Common errors and practical checks
When a filter appears to do nothing or affects the wrong rows, check the range and field numbering first. Then verify the data itself: text values may contain leading or trailing spaces, numbers stored as text may behave differently from numeric values, and blanks may not mean the same thing as cells containing a formula that returns an empty string.
- Confirm that the range begins at the header row and covers every data column and row.
- Count
Fieldfrom the range’s left edge, not from worksheet column A. - Check that the header row is a single row of meaningful column labels.
- Use
xlAndorxlOrexplicitly when combining two criteria. - Check for hidden rows or other active field filters when the visible results seem incomplete.
- Do not assume that a blank-looking cell is truly empty; a formula can return an empty string.
Another common misconception is that calling AutoFilter always clears every filter. Calling it with a new criterion changes the filter for the specified field, while filters on other fields may remain active. If the aim is to start from an unfiltered list, clear existing criteria deliberately before applying the new ones.
Excel for Mac and alternative approaches
This article targets Excel for Microsoft 365 on Windows. Microsoft documents the Range.AutoFilter method as unsupported on Excel for Mac, while similar methods on Selection and ListObject are supported. If a range-based call fails on a Mac, use the supported object approach for the Excel version in use, such as calling AutoFilter on a selected range or on a table’s range, and verify the result in that Mac version. The VBA editor and some interface details differ between Windows and Mac, but the filter criteria concepts are the same.
For routine list filtering, the Excel interface may be simpler: select a cell in the data and choose Data > Filter on Windows, then use a column’s dropdown to choose criteria. VBA is useful when filtering must be repeated, combined with other macro steps, or applied to several reports consistently. If data is already an Excel Table, its built-in filter controls may be sufficient without any code.
Frequently Asked Questions
What is the simplest VBA AutoFilter example?
Include the header row in the range and use a field number counted from its left edge, for example Range("A1:D100").AutoFilter Field:=2, Criteria1:="West".
Does Field mean the worksheet column number?
No. It is the one-based position within the range passed to AutoFilter. If the range starts in column B, column B is Field 1 regardless of its worksheet address.
How do I filter for two values in one column?
For multiple exact choices, use an array with Operator:=xlFilterValues, such as Criteria1:=Array("East", "West"). For two comparison conditions, use Criteria1, Criteria2, and an appropriate operator.
How can I remove a filter but keep the dropdown arrows?
If the worksheet is filtered, use Worksheet.ShowAllData to reveal the rows. Do not set AutoFilterMode to False unless you also want to remove the arrows.
Why does Range.AutoFilter fail on Excel for Mac?
Microsoft’s documentation states that the Range version of the method is not supported on Mac. Use a supported Selection or ListObject approach, and confirm the syntax against the Excel version being used.
Related
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Selection in Excel: Code and Common FixesTo sort a selected range in Excel with VBA, use the range’s Sort method and specify a sort…
- Excel VBA New Line in a String: Practical ExamplesTo add a new line to a string in Excel VBA, join the text with a newline constant:…
- Excel VBA: Create an Outlook Email Draft in WindowsYes. In Excel for Microsoft 365 on Windows, VBA can use classic Outlook’s object model to create an…
- VBA MsgBox Font Size: What You Can and Cannot ChangeYou cannot set the font size of VBA’s built-in MsgBox in Excel: the function has no font-size argument…
- VBA MsgBox Yes/No: Continue If the User Clicks YesUse VBA’s MsgBox with the vbYesNo button option, save the result, and put the work that should continue…
- Is Excel Macro Recorder Safe to Use? Risks and LimitsExcel’s Macro Recorder is generally safe to use for recording your own routine actions, but it does not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.