In VBA, AutoFilter criteria are the conditions you pass to the AutoFilter method to show matching rows. For example, use Criteria1:="West" to keep rows whose selected field contains West; the field number is counted from the left edge of the filtered range, not from the worksheet’s column A.
What AutoFilter criteria control
Excel’s AutoFilter hides rows that do not meet a condition; it does not delete those rows or change their cell values. In VBA, the Range.AutoFilter method applies a condition to a range with a header row. Its basic pattern is:
expression.AutoFilter Field:=fieldNumber, Criteria1:=condition
Field identifies the column being filtered, using a one-based position within the range. If the range is A1:D100, Field 1 refers to column A, Field 2 to B, and Field 4 to D. If the range starts at column C, Field 1 refers to C. This relative numbering is one of the most common sources of filters that appear to target the wrong column.
A criterion can be an exact text value, a comparison such as greater than a number, a wildcard pattern, or a blank/nonblank test. To filter multiple fields, call AutoFilter again on the same range with a different Field number; Excel combines the field conditions so rows must satisfy both.
Basic syntax and a first example
For Microsoft 365 Excel on Windows, use a range that includes the header row and all records you want to filter. The worksheet name and range address should be explicit rather than relying on whichever cells happen to be selected. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.
Sub FilterSalesByRegion()
Dim ws As Worksheet
' The range includes its header row in row 1.
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("A1:D100").AutoFilter Field:=2, Criteria1:="West"
End Sub
This example filters the second field in A1:D100, which is column B, for the exact text West. It assumes row 1 contains headers and the region values are in column B. If the data can grow beyond row 100, use a larger range or, preferably, an Excel table so new records are included without editing the macro.
The arguments commonly used are Field, Criteria1, Operator, and Criteria2. The operator is optional for a single condition. When you use two conditions on one field, the operator determines whether both conditions must be true or either one may be true.
Using two conditions on one field
Use xlAnd for a range between two limits, and xlOr when either of two separate conditions should match. In the example below, the field is the third column of A1:D100. It displays values from 10 through 20, including both endpoints.
Sub FilterValuesFrom10To20()
Dim ws As Worksheet
' Save a workbook copy before running a macro.
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("A1:D100").AutoFilter _
Field:=3, Criteria1:=">=10", Operator:=xlAnd, Criteria2:="<=20"
End Sub
Criteria such as ">500", "<=100", and "<>0" express greater-than, less-than-or-equal-to, and not-equal-to comparisons. Although the condition looks like a string in VBA, Excel interprets the comparison operator when it evaluates the filter.
For a short list of allowed values in one field, use an array with xlFilterValues. This avoids writing a chain of separate comparisons:
Sub FilterTwoRegions()
Dim ws As Worksheet
' Filters the second field to either listed value.
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("A1:D100").AutoFilter _
Field:=2, Criteria1:=Array("North", "South"), Operator:=xlFilterValues
End Sub
For different fields, apply another condition to the same range. For instance, filter Field 2 for West, then Field 3 for values above 500. Both conditions remain active, so visible rows match West and exceed 500. Reusing exactly the same range matters: applying the second filter to a differently sized or shifted range can produce confusing results.
Text criteria, wildcards, and exact matches
A plain text criterion such as "West" generally matches that field value, without case being a useful way to distinguish records in an ordinary AutoFilter. To find text that contains, begins with, or ends with a word, use wildcard characters:
*east*matches text containing east, such as Northeast or Eastern.North*matches text beginning with North.*Ltdmatches text ending in Ltd.?represents one character, soAB?can match ABC or AB7.
The tilde (~) is the escape character for a literal wildcard. For example, ~* searches for a literal asterisk rather than treating it as “any sequence of characters.” Be aware that leading or trailing spaces in source data can prevent an apparent exact match. If data comes from imports, inspect the actual cell contents or clean the values before filtering.
Number, date, and blank criteria
Numeric comparisons work when the cells contain numbers, not numbers stored as text. A criterion such as ">=1000" will not reliably behave like a numeric test if the source column contains text strings that look like numbers. Check the cells’ underlying values and data types when a comparison returns unexpected rows.
Dates in Excel are stored as serial numbers, and cells may also include a time fraction. For a date interval, an inclusive start and an exclusive next-month boundary is often safer than trying to express the final moment of the last day. This example filters the third field for January 2025:
Sub FilterJanuary2025()
Dim ws As Worksheet
' The date column must contain real Excel dates.
Set ws = ThisWorkbook.Worksheets("Sales")
ws.Range("A1:D100").AutoFilter _
Field:=3, _
Criteria1:=">=" & CLng(DateSerial(2025, 1, 1)), _
Operator:=xlAnd, _
Criteria2:="<" & CLng(DateSerial(2025, 2, 1))
End Sub
The condition includes midnight on January 1 and excludes midnight on February 1, which includes January dates that have time values as well. This example assumes a standard 1900-date-system workbook and real Excel date values; text dates or a workbook using the 1904 date system require extra care. When filtering dates, confirm the workbook’s date system and test the criteria against representative records.
To show blank cells in a field, use Criteria1:="="; to show nonblank cells, use Criteria1:="<>". These tests concern cells AutoFilter recognizes as blank or nonblank. A formula that returns an empty string may not behave exactly like a truly empty cell in every filtering scenario, so check the actual source values if the results are surprising.
Range headers and field numbering
AutoFilter expects the first row of its range to contain the column headings. If you omit the header row, Excel may treat the first data record as a heading, leaving it out of the normal filtered records. Avoid merged header cells and duplicate or blank headings; clear, unique labels make filters and later VBA maintenance easier.
Field numbers are positions, not worksheet column numbers. For example, with C1:F50, Field 1 is C, Field 2 is D, Field 3 is E, and Field 4 is F. A useful debugging habit is to write down the filtered range’s first column and count across before changing the Field argument.
Fixed addresses are fine for a stable report, but are brittle when rows are appended. An Excel table expands as records are added and gives VBA a structured object to target. For table-based data, you can refer to a table by name and apply the filter to its range; ensure that the field index still reflects the table’s own left-to-right column order.
Tables, existing filters, and clearing criteria
A worksheet can already have filter arrows or active conditions when a macro runs. Calling AutoFilter with a criterion applies or updates a condition; it does not delete records. If a macro is intended to start from a clean state, clear existing criteria deliberately rather than assuming the sheet is unfiltered.
To show all rows while keeping the filter arrows, use the worksheet’s ShowAllData method only when a filter is active. For example:
Sub ClearActiveFilters()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales")
If ws.FilterMode Then ws.ShowAllData
End Sub
FilterMode indicates whether the worksheet is currently showing a filtered subset. By contrast, setting AutoFilterMode to False removes the filter arrows as well as the filtering interface, so it is not the same operation. With tables, use the table’s filter controls or methods appropriately; worksheet-level filtering and table filtering can have different scopes.
Common problems and how to diagnose them
- Wrong column filtered: Recount Field from the left edge of the exact range passed to AutoFilter.
- No rows match: Check spelling, leading/trailing spaces, numbers stored as text, and whether the range includes the records you expect.
- First record disappears: Make sure the first row in the range is the header, not a data row.
- New records are ignored: Expand the range or use a table that includes newly added rows.
- Old filters remain: Clear the previous conditions explicitly before applying a new independent filter.
- Dates seem inconsistent: Confirm the cells contain Excel dates, check time values and the workbook date system, and avoid ambiguous date strings.
Also confirm that the worksheet name and range are correct and that the workbook is not in a state that prevents the macro from changing the data view. If a criterion is built from a cell value, inspect the final criterion string in the VBA editor’s Immediate window or with a temporary message box. This can reveal an unexpected space, an incorrectly formatted date, or a comparison operator missing from the string.
Windows, Mac, and Excel for the web
The code examples use Microsoft 365 Excel for Windows. Microsoft documents the Range.AutoFilter method as unsupported on Excel for Mac, while related AutoFilter methods for a selection or a table are available. On Mac, adapt the method to the actual object and Excel version, and test the macro on a copy; table-based filtering can be a practical option. VBA macros cannot run in Excel for the web, so use desktop Excel to run or edit them.
The filter criteria themselves follow Excel’s AutoFilter conventions, but platform support and object-method behavior can differ. If a macro must run on both Windows and Mac, design around the supported table or selection methods, avoid assumptions about platform-specific behavior, and verify the result in each target desktop version.
Frequently Asked Questions
What does Field mean in VBA AutoFilter?
Field is the one-based position of the column inside the range passed to AutoFilter. It is not necessarily the worksheet column number: Field 1 means the range’s first column.
How do I filter for more than one value in the same column?
Pass an array of values as Criteria1 and set Operator to xlFilterValues, for example North and South. For a condition based on two comparisons, use Criteria1, Criteria2, and xlAnd or xlOr.
How do I filter for blank cells?
Use Criteria1:="=" for blanks and Criteria1:="<>" for nonblanks. Formula results that look empty can require separate checking.
Does AutoFilter delete rows that do not match?
No. It hides nonmatching rows from view; the data remains in the worksheet. Do not use a filter as a substitute for a deliberate deletion procedure.
Why does my VBA AutoFilter use the wrong column?
The Field number is relative to the filter range. If the range begins in column C, Field 1 means C, not A; verify both the range address and its left-to-right field order.
Can I run VBA AutoFilter in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to execute the code; Mac users should account for the documented difference in support for the Range.AutoFilter method.
Related
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
- Excel VBA: Format a Range as a DateTo format a range as dates with Excel VBA, assign a date format to its NumberFormat property, for…
- Excel VBA List Worksheet Names: Methods and ExamplesTo list worksheet names with Excel VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name…
- Excel VBA: Find the Minimum Value in a Worksheet RangeIn Excel VBA, use Application.WorksheetFunction.Min to get the smallest number in a range. For example, if the values…
- VBA InputBox Function: Syntax, Examples, and LimitsThe VBA InputBox function displays a dialog box that asks a user to enter text, then returns the…
- VBA Sort Collection: Methods and Practical ExamplesVBA’s Collection object has no built-in sort method. To sort a collection, copy its items into an array,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.