In Excel VBA, set AutoFilter criteria with the Range.AutoFilter method: specify a filter range that includes its header, a Field number relative to the range’s first column, and a criterion such as Criteria1:=">500". Use Operator:=xlAnd or xlOr with a second criterion when you need two conditions on the same field.
How AutoFilter criteria work
AutoFilter hides rows that do not meet the conditions; it does not delete them. The range supplied to AutoFilter should include the header row and the records to filter. The Field argument is a column position within that range, not a worksheet column number. For example, if the range starts in column B, Field:=1 refers to column B and Field:=3 refers to column D.
A criterion is usually a text value, a comparison string, or a wildcard pattern. In VBA, named arguments make the code easier to read, and the operator belongs between the first and second criteria when you use two conditions.
Sub FilterHighSales()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' The range includes headers; Field 4 is its fourth column.
ws.Range("A1:D100").AutoFilter Field:=4, Criteria1:=">500"
End Sub
This example shows rows whose value in the fourth field is greater than 500. A filter range that starts at A1 and ends at D100 must have the headers in row 1; change the range and field number to match the actual data layout. Before running a macro, save a copy of the workbook: macros cannot be undone with Ctrl+Z.
Choosing a criterion for text and numbers
For text, a plain criterion such as "East" selects matching entries in that field. For numeric values, use comparison operators as part of the criterion string. The common operators are >, >=, <, <=, and <> (not equal). Put the operator and value together, as in ">=100" or "<>0".
- Exact text:
Criteria1:="East" - Greater than a threshold:
Criteria1:=">500" - Not equal to a value:
Criteria1:="<>0" - Text beginning with a phrase:
Criteria1:="North*"
Be mindful of the data type in the cells. A number stored as text may not behave like a numeric value during comparison, and visually identical entries can differ because of extra spaces or inconsistent spelling. If the criterion seems correct but rows are missing, inspect the source cells and confirm that the field contains consistent data.
Using AND and OR conditions
For two conditions on the same field, use xlAnd when both must be true, or xlOr when either may be true. For example, a score from 60 up to but not including 90 can be filtered with two numeric boundaries. The exclusive upper limit avoids including 90.
Sub FilterScoreBand()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Field 3 must be at least 60 and less than 90.
ws.Range("A1:D100").AutoFilter Field:=3, _
Criteria1:=">=60", Operator:=xlAnd, Criteria2:="<90"
End Sub
For two text alternatives on the same field, use xlOr with Criteria1 and Criteria2. When the list of allowed values is longer, pass an array and use xlFilterValues instead of stacking many two-condition filters:
Sub FilterSelectedRegions()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Keep records whose second field is East or West.
ws.Range("A1:D100").AutoFilter Field:=2, _
Criteria1:=Array("East", "West"), Operator:=xlFilterValues
End Sub
Conditions applied to different fields are combined as AND conditions. For instance, filtering a Region field and then a Sales field keeps rows that satisfy both the region selection and the sales threshold. Make each call against the same filter range, and calculate each field number from that range’s left edge.
Wildcards, blanks, and literal characters
AutoFilter criteria support wildcards for text. An asterisk (*) represents any number of characters; a question mark (?) represents one character. Thus "Sam*" can match “Sam” and “Samantha,” while "A?C" can match “ABC” but not “AC.” To search for a literal wildcard character, prefix it with a tilde: "~*" matches an asterisk character rather than treating it as a wildcard.
Blank and nonblank cells can be filtered with the special criteria "=" and "<>", respectively. These target empty and nonempty cells in the selected field. In a real worksheet, a formula that returns an empty string may not behave exactly like a truly empty cell, so check the underlying data if the results surprise you. A space character is also not the same as an empty cell.
Filtering dates safely
Excel stores dates as serial numbers, but users generally see formatted dates. Hard-coding a date such as "1/2/2025" into a criterion can be ambiguous because regional settings may interpret it as January 2 or February 1. For a date interval, one practical option is to build boundaries with VBA’s DateSerial and use their numeric serial values. This example includes dates from January 1, 2024, through December 31, 2024, by using January 1, 2025 as an exclusive endpoint.
Sub FilterDatesIn2024()
Dim ws As Worksheet
Dim startDate As Long
Dim endDate As Long
Set ws = ThisWorkbook.Worksheets("Data")
startDate = CLng(DateSerial(2024, 1, 1))
endDate = CLng(DateSerial(2025, 1, 1))
' Field 1 contains Excel dates; the upper boundary is exclusive.
ws.Range("A1:D100").AutoFilter Field:=1, _
Criteria1:=">=" & startDate, Operator:=xlAnd, _
Criteria2:="<" & endDate
End Sub
This approach assumes the cells contain real Excel date values, rather than date-looking text. If the source column contains text dates, convert or normalize that data before relying on date comparisons. Also account for any time component: the exclusive next-day boundary includes every time on the last day of the requested interval.
Filtering a range or an Excel table
A standard worksheet range works well when the data occupies a predictable block. If rows are added regularly, an Excel Table is often more convenient because the table expands as new records are entered. In VBA, a table is represented by a ListObject; its Range includes the table headers and data. The field number for a table filter is still relative to the table’s first column, not to the worksheet’s column letters.
Choose a range that contains one header row and a consistent set of columns. Avoid including unrelated notes or a second header row, since those can be treated as records. If the range is built dynamically, determine its last row and last column from the data rather than assuming a fixed endpoint. Excel for Microsoft 365 on Windows supports the VBA examples here; Excel for the web does not run VBA macros. Microsoft documents the Range.AutoFilter method as unsupported on Excel for Mac, so do not assume this Windows VBA code will run unchanged there.
Clearing filters and keeping the dropdowns
There is an important difference between clearing the current filter results and removing the filter controls. ShowAllData displays all rows again while leaving the AutoFilter dropdowns available. Setting AutoFilterMode to False removes the dropdown arrows as well. Check FilterMode before calling ShowAllData, because calling it when no filter is active can raise an error.
Sub ClearCurrentFilter()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Show all rows but retain the filter arrows.
If ws.FilterMode Then ws.ShowAllData
End Sub
To remove the arrows intentionally, use ws.AutoFilterMode = False after considering whether the worksheet’s filter controls should remain. That setting is not a substitute for showing all rows while preserving the existing filter interface.
Common AutoFilter criteria problems
Many apparent VBA errors are caused by a mismatch between the filter range and the worksheet layout. Check these points before rewriting the criterion:
- Wrong field index: Field numbers start at 1 for the leftmost column of the selected range, not at worksheet column A.
- Header omitted: Include the header row in the range so Excel can identify the fields correctly.
- Text versus number: Confirm that numeric data is stored as numbers and not as text.
- Wrong comparison logic: Two bounds that should both apply require
xlAnd;xlOrreturns rows meeting either condition. - Existing filters: A prior filter on another field remains in effect, so the visible results may reflect several conditions.
- Misspelled values or spaces: Check for leading/trailing spaces and inconsistent labels in the source column.
Also verify that the worksheet name and range address are correct, and that the data is on the worksheet referenced by the macro. If a filter is applied to a table, use the table’s own range and the table-relative field index. When diagnosing a problem, clear existing filters, apply one criterion, and then add conditions one at a time; this makes it easier to identify which condition is responsible.
Frequently Asked Questions
What does Field mean in VBA AutoFilter?
Field is the one-based column position inside the range passed to AutoFilter. If the range starts at C1, Field 1 refers to column C, regardless of its worksheet column number.
How do I filter for two values in one column?
Use Operator:=xlOr with two criteria for two choices, or use Criteria1:=Array("East", "West"), Operator:=xlFilterValues for a list of selected values.
How do I filter values between two numbers?
Pass a lower and upper comparison in Criteria1 and Criteria2, with Operator:=xlAnd. For example, ">=60" and "<90" select values from 60 up to, but not including, 90.
Does AutoFilter delete the rows that do not match?
No. AutoFilter hides nonmatching rows. Clearing the filter shows them again; it does not restore rows that were separately deleted by other code.
Why does my filter show no matching rows?
Check the field index, header-inclusive range, cell data types, spelling, spaces, date interpretation, and any filters already active on other fields. Each of these can change which rows remain visible.
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
- Left in Excel VBA: Syntax, Examples, and Common IssuesIn Excel VBA, Left returns a specified number of characters from the beginning of a string. For example,…
- Access VBA String Functions: Examples and Practical UsesAccess VBA string functions are built-in routines for finding, extracting, comparing, formatting, and changing text. Common choices include…
- VBA Find a Value in a Range: Excel Code and ExamplesIn VBA, use Excel’s Range.Find method to search a range and return the matching cell; check whether the…
- Excel VBA: How to Copy a UserForm to Another WorkbookTo copy an Excel VBA UserForm to another workbook, export the form from the source workbook’s VBA project…
- Excel VBA FindNext: How to Find the Next MatchIn Excel VBA, use Range.FindNext to move from one match to the next after starting a search with…
- Excel VBA: Find the Last Row With Data in a ColumnIn Excel VBA, the usual way to find the last occupied row in one column is to start…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.