To filter multiple Excel fields with VBA, call AutoFilter on the same range once for each column, using that column’s Field number and criteria. For example, filter a sales list to Region = East and Status = Open with Field 2 and Field 4; filters on different fields are combined, so rows must meet both conditions.
How AutoFilter works across fields
AutoFilter adds filter controls to a heading row and hides rows that do not match the selected criteria. In VBA, the range on which you call AutoFilter defines the whole filtered list, including its headers. The Field argument is a position within that range, counted from its leftmost column—not necessarily the worksheet’s column letter or number.
Suppose the data range is A1:D100, with Date in A, Region in B, Product in C, and Status in D. In that range, Region is Field 2 and Status is Field 4. If you instead filter B1:E100, Region becomes Field 1. Getting this relative numbering right is one of the most important details when filtering several columns.
Each call that specifies a different field sets a condition for that field. Excel keeps the other field conditions in place, so a row remains visible only when it satisfies all active field filters. This is effectively an AND between fields: Region = East AND Status = Open. The criteria within one field can use different logic, as described below.
A basic VBA example
This macro assumes a worksheet named Sales, headers in row 1, and data in columns A through D. It filters the list to show East-region rows whose status is Open. Change the sheet name, range, field numbers, and criteria to fit your workbook.
Sub FilterEastOpen()
Dim ws As Worksheet
Dim dataRange As Range
Set ws = ThisWorkbook.Worksheets("Sales")
Set dataRange = ws.Range("A1:D100")
' Field numbers are relative to dataRange.
dataRange.AutoFilter Field:=2, Criteria1:="East"
dataRange.AutoFilter Field:=4, Criteria1:="Open"
End Sub
The first call filters Region, and the second adds a Status condition to the same range. If the data has a different number of rows, adjust the range so it includes the header and every row to filter. This fixed-range example does not automatically discover new rows added below row 100.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a filter can change what is visible even though it does not delete the excluded rows. On Windows, open the Visual Basic Editor with Alt+F11; on Mac, Option+F11 may work, sometimes with the Fn key depending on the keyboard. Excel for the web cannot run VBA macros.
Choose a stable range or an Excel table
A fixed range is simple, but it can become outdated when records are added or removed. For a growing data set, format the list as an Excel table with Insert > Table, then use the table’s range in VBA. Tables expand as rows are added and provide named columns, which makes the setup easier to maintain.
For a table named SalesTable on the Sales worksheet, the equivalent filtering calls can use its range:
Sub FilterSalesTable()
Dim ws As Worksheet
Dim salesTable As ListObject
Set ws = ThisWorkbook.Worksheets("Sales")
Set salesTable = ws.ListObjects("SalesTable")
' The table range includes its header row.
salesTable.Range.AutoFilter Field:=2, Criteria1:="East"
salesTable.Range.AutoFilter Field:=4, Criteria1:="Open"
End Sub
Field numbers for a table are relative to the table’s columns. If the table has columns in the same order as the earlier example, Field 2 is Region and Field 4 is Status. A table’s filter arrows are usually already present, but calling AutoFilter with a field and criteria applies the requested condition.
Combining AND and OR criteria
Filters on separate fields combine with AND. A row must satisfy every active field condition. To allow either of two values in the same field, use Criteria1 and Criteria2 with Operator:=xlOr. For example, the following condition shows rows where Region is East or West:
dataRange.AutoFilter Field:=2, Criteria1:="East", _
Operator:=xlOr, Criteria2:="West"
That OR applies within the Region field; it does not change the AND relationship with a filter on another field. Combined with Status = Open, the result is (Region = East OR Region = West) AND Status = Open.
For a list of multiple exact text values in one field, AutoFilter also accepts an array with Operator:=xlFilterValues:
dataRange.AutoFilter Field:=2, _
Criteria1:=Array("East", "West", "North"), _
Operator:=xlFilterValues
Use this pattern for a short set of allowed values. It is different from applying separate field filters: the array selects several alternatives in one column, while calls on different fields narrow the result together. If values come from a worksheet range and change often, a longer macro can build the array from those cells rather than hard-coding each item.
Text, numbers, wildcards, and dates
For a straightforward text match, pass the desired value as a string, such as Criteria1:="Open". To find text containing a word, AutoFilter criteria can use wildcards: an asterisk represents any number of characters, and a question mark represents one character. For example, Criteria1:="*urgent*" matches text containing “urgent.” A tilde escapes a wildcard when you need to search for a literal asterisk or question mark.
Numeric criteria can use comparison operators in the criteria string. For example, Criteria1:=">=500" selects values at least 500, while Criteria1:="<1000" selects values below 1,000. Two comparisons on the same numeric field can define a band; use Operator:=xlAnd with a lower and upper criterion when both limits must be met. Check that the column contains numeric values rather than numbers stored as text, since mixed data can produce results that seem inconsistent.
Date filtering deserves extra care. Excel stores dates as serial values, but VBA AutoFilter criteria may be interpreted according to the workbook’s date data and regional settings. A criterion written with a slash-formatted date can behave differently across locale settings. For reliable date filters, confirm the workbook’s date format and test with representative dates; avoid assuming that a date string copied from one computer will parse identically everywhere. If the requirement is a date interval, use two comparison criteria with an AND operator and verify the visible results.
Clear criteria without removing filter arrows
There are two different operations that are often confused: clearing the current filter selections and removing the AutoFilter controls. To show all rows while leaving the dropdown arrows in place, use the worksheet’s ShowAllData method when the sheet is currently filtered:
Sub ClearCurrentFilters()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales")
' ShowAllData is only needed when rows are filtered.
If ws.FilterMode Then
ws.ShowAllData
End If
End Sub
FilterMode indicates whether the worksheet is currently displaying a filtered subset. By contrast, AutoFilterMode indicates whether filter arrows are displayed. Setting ws.AutoFilterMode = False removes those arrows; it is not the same as simply clearing the criteria. Use that property only when you intend to remove the filter controls.
When clearing a table’s filters, consider whether other tables or filtered ranges are on the same worksheet. A worksheet-level call can affect the worksheet’s current filtered display, so avoid clearing everything if the macro should preserve unrelated filters. If your goal is only to change one field, apply the new criterion to that field instead of clearing the whole sheet first.
Common problems and how to diagnose them
- Wrong Field number: Count from the left edge of the range passed to AutoFilter. Field 1 is the range’s first column, even if that column is worksheet column C.
- Header row omitted: AutoFilter expects a heading row in the range. Include it and check that each heading is in the first row of the filtered range.
- Different ranges in different calls: Apply all field conditions to the same list range. Using inconsistent ranges can produce errors or filters that do not behave as intended.
- Existing conditions remain: A new criterion on one field does not automatically erase conditions on other fields. Clear them deliberately if you want a fresh, unfiltered starting point.
- Criteria do not match the cell contents: Check for extra spaces, inconsistent spelling, blanks, or values stored in a different data type. “Open” and “Open ” are not the same text value.
- No rows appear: The conditions may be individually valid but have no records in common. Test one field at a time, then add the other filters back.
- New records are not included: A hard-coded range stops at its last specified row. Expand it or use an Excel table so added records are included more naturally.
For a macro that users run repeatedly, consider checking that the worksheet and table exist before filtering, and give a helpful message if they do not. Also decide whether the macro should preserve filters the user already selected or clear them first. That is a design choice: preserving them respects the current view, while clearing them makes the macro’s result more predictable.
VBA AutoFilter compared with worksheet filtering
The same basic filtering logic is available through Excel’s dropdown arrows, so VBA is not required just to filter a list once. Manual filtering is useful when a person wants to inspect different values interactively. VBA is more useful when the same combination of criteria must be applied repeatedly, when a button should run a standard report, or when filtering is one step in a larger process.
Excel tables make manual filtering convenient and keep the list structured; VBA can automate those table filters without changing the underlying records. In either case, filtering hides nonmatching rows rather than removing them. If later code copies only visible cells, deletes rows, or exports results, those actions need their own careful handling—the filter itself is not a data-deletion command.
These VBA instructions apply to desktop Excel for Microsoft 365 on Windows. Excel for Mac supports VBA and AutoFilter, though keyboard shortcuts and some macro-related environment details can differ. Excel for the web can display a workbook containing macros but does not execute VBA; use desktop Excel to run the code.
Frequently Asked Questions
Can I filter multiple columns in one AutoFilter call?
In common VBA use, apply one call per field to the same range, specifying each field’s criterion. This keeps the code readable and makes it clear which column each condition affects.
Does filtering two fields mean AND or OR?
Different fields are combined as AND conditions: a row must meet both. Use an OR operator or an array of values when you want alternatives within a single field.
Does Field:=2 mean worksheet column B?
Not always. It means the second column within the range used for AutoFilter. If the range begins in column C, Field 2 refers to worksheet column D.
How do I filter a table by multiple columns?
Get the table as a ListObject, then call AutoFilter on its Range for each field. Number fields relative to the table’s first column, and use the same table range for each call.
How do I remove the filter but keep the dropdowns?
When the worksheet is filtered, call ShowAllData to display all rows while retaining the filter controls. Setting AutoFilterMode to False removes the arrows instead.
Can VBA filters run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to execute the macro.
Related
- Excel Text to Columns: Why Leading Zeros Disappear
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Collection: How It Works and When to Use ItAn Excel VBA Collection is an in-memory container that holds related items and lets you retrieve them by…
- Excel VBA On Error GoTo: How Error Handling WorksIn Excel VBA, On Error GoTo LabelName sends execution to a named error-handling section when a run-time error…
- VBA Copy and Paste Values Only: Practical Methods and CodeTo copy values only with VBA, assign the source range’s values to an equally sized destination range: destinationRange.Value…
- VBA Delete Row If Cell Contains TextTo delete rows when a cell contains particular text, use VBA’s InStr function in a loop that checks…
- Excel VBA: Loop Through Rows to the Last RowTo loop through data rows in Excel VBA, find the last nonempty cell in a reliable column, then…
- Excel VBA Unique List: How to Create One ReliablyTo create a unique list with Excel VBA, read the source range, keep the first occurrence of each…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.