In Excel VBA, use the AutoFilter method on a range that includes the header row to filter records by a column’s value. For example, Range("A1:D100").AutoFilter Field:=3, Criteria1:="West" keeps rows whose third field is West visible; AutoFilter hides entire rows, not individual cells.
What VBA AutoFilter Does
AutoFilter is Excel’s built-in way to show only rows that meet criteria in one or more columns. In VBA, the Range.AutoFilter method applies those criteria to a worksheet range. Excel adds or uses filter controls in the header row, then hides records that do not match. The values remain in the worksheet: filtering changes which rows are displayed, not the underlying data.
This distinction answers a common interpretation of “filter cells.” You choose criteria based on the cells in a field, but Excel filters the corresponding rows as complete records. If a row has a matching region in column C, its customer, date, and amount in the other columns remain visible with it. AutoFilter is useful for inspecting, copying, or processing a subset of a list without deleting the other records.
For Microsoft Excel for Microsoft 365 on Windows, VBA macros run in the desktop application. Excel for the web can display a workbook containing macros, but it cannot run or edit VBA macros. Excel for Mac supports VBA, though available keyboard shortcuts and some interface details can differ.
Understand the Range and Field Number
The range passed to AutoFilter should normally include the column headings and the rows of data beneath them. In Range("A1:D100"), row 1 is the header row and rows 2 through 100 are records. The Field argument is a position within that range, counted from its left edge—not the worksheet’s column number. Thus, Field 1 means column A, Field 2 means B, and Field 3 means C for this particular range.
That relative numbering is a frequent source of mistakes. If your range starts at column C, then Field 1 refers to C, not A. Likewise, filtering a range that starts at A with Field 3 means column C even if the worksheet has other content elsewhere. Check both the range boundaries and the header row before adjusting the field number.
When the list is an Excel table, its range and headers are managed by Excel. You can refer to the table’s range with its ListObject, which is often more reliable than hard-coding the final row. For ordinary ranges, a fixed range is straightforward when the data size is stable; a dynamic range is preferable when rows are regularly added.
Apply a Basic Filter with VBA
This example filters a worksheet named Data, using the range A1:D100. It selects records whose third field equals West. Before running any macro, save a copy of the workbook: VBA actions generally cannot be reversed with Ctrl+Z.
Sub FilterWest()
Dim ws As Worksheet
' Apply a filter to the range, including its header row.
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("A1:D100").AutoFilter Field:=3, Criteria1:="West"
End Sub
The range must match your actual data; change the worksheet name, address, field number, and criterion as needed. If the range already has filter arrows, this call applies or changes the filter for the specified field. Filters on other fields can remain in place, so the displayed records must meet all active column filters at once.
To run a macro in desktop Excel, open the Visual Basic Editor with Alt+F11 on Windows, insert the code in a standard module, and run the procedure from the editor or Excel’s Macro dialog. On a Mac, Option+F11 commonly opens the Visual Basic Editor; some keyboards require Fn as well. You may need to enable the Developer tab or adjust macro security settings. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).
Choose Criteria for Text, Numbers, and Blanks
The Criteria1 argument specifies what the selected field should match. Text criteria can be supplied as a string, while comparison criteria for numbers are written as text with an operator. Excel supports operators such as greater than, less than, and not equal to. Wildcards can match text patterns: an asterisk represents any sequence of characters, and a question mark represents one character.
- Exact text:
Criteria1:="West"matches the West value in the selected field. - Numbers above a threshold:
Criteria1:=">500"selects values greater than 500. - One of two conditions: use
Operator:=xlOrwithCriteria1andCriteria2, for example West or East. - Values within a numeric interval: use
Operator:=xlAndwith criteria such as greater than or equal to 100 and less than 500. - Blank cells:
Criteria1:="="selects blanks;Criteria1:="<>"selects nonblank values.
For example, if column C contains regions, a two-value OR filter can be written as ws.Range("A1:D100").AutoFilter Field:=3, Criteria1:="West", Operator:=xlOr, Criteria2:="East". If you want a list of several allowed values, AutoFilter also supports an array of criteria with Operator:=xlFilterValues. The criteria need to match the data’s actual contents; an extra space or inconsistent spelling can produce fewer matches than expected.
Excel’s standard text filtering is generally not case-sensitive. A normal AutoFilter criterion is therefore not a dependable way to distinguish values such as West and WEST. If case-sensitive matching is essential, use a helper column with an explicit comparison or write separate VBA logic rather than assuming AutoFilter will treat capitalization as distinct.
Use a Table or a Dynamic Range
For a formatted Excel table, use the table’s range instead of guessing how many rows are present. For instance, if a table named SalesTable has a Region field in its second column, you can apply the filter to ListObjects("SalesTable").Range with Field:=2. The field number is still relative to the range, and the table range includes its header row.
A regular range can be expanded at runtime, but choose the method to fit the sheet’s layout. CurrentRegion finds a contiguous block around a starting cell; it can stop at a completely blank row or column, and it can include unrelated adjacent content. It is convenient for a clean, uninterrupted list, but not a universal way to locate data. An Excel table is usually clearer when the list grows over time because new table rows become part of the table automatically.
Before applying a filter, verify that the first row of the selected range contains unique, meaningful headings and that there are no merged cells or unexpected blank rows splitting the list. A range that starts one row too high or low can make the wrong row act as the header. That can lead to confusing dropdown labels or a field number that appears to target the wrong column.
Clear Filters and Read Visible Cells
There are two related worksheet properties to understand. FilterMode indicates that the sheet is currently displaying a filtered subset; AutoFilterMode indicates that AutoFilter arrows are displayed. Clearing criteria to show all records is different from removing the dropdown arrows. Use a guarded call to ShowAllData when you only want to reveal the filtered-out rows:
Sub ShowAllRecords()
Dim ws As Worksheet
' Restore rows hidden by active filters, if any.
Set ws = ThisWorkbook.Worksheets("Data")
If ws.FilterMode Then ws.ShowAllData
End Sub
After filtering, code can work with visible cells only by using SpecialCells(xlCellTypeVisible). Be deliberate about the range: applying it to the whole filtered range may include the header, while applying it to the data body avoids that. If no data rows are visible, SpecialCells can raise an error, so handle that case explicitly before copying or processing the result. Also remember that visible cells can form multiple separate areas because hidden rows create gaps.
Filtering and selecting visible cells are separate operations. AutoFilter decides which rows to hide; SpecialCells identifies cells that remain visible. This distinction matters when exporting results or looping through a filtered subset. A loop over every row in the original range may still encounter hidden rows unless the code checks visibility or works from a visible-cells range.
Common Problems and How to Fix Them
- The wrong column is filtered: Recount Field from the first column of the AutoFilter range, not from worksheet column A.
- No records match: Check spelling, spaces, data types, and the selected range. A number stored as text may not behave like a numeric value.
- Headers appear among the results: Confirm the range begins on the header row and includes headers, rather than beginning with the first data record.
- New rows are not included: Expand a hard-coded range or use an Excel table whose data range grows with added records.
- ShowAllData causes an error: Call it only when
FilterModeis True; the example above includes that check. - A filter seems to remove data: AutoFilter hides nonmatching rows; it does not delete them. Clear the filter to display them again.
Another source of confusion is an existing filter on a different field. Applying a Region filter while a Date filter is already active displays rows meeting both criteria, not either one. If the result looks unexpectedly small, clear the other filters or inspect each field’s dropdown before changing the VBA criteria.
AutoFilter Compared with Other Approaches
AutoFilter is a good fit when the goal is to interact with a list or hide nonmatching records in place. It is usually simpler than writing a loop that tests every row and manually hides those that fail. It also preserves the source values and works naturally with Excel’s filter dropdowns, allowing a user to adjust criteria afterward.
Use an Advanced Filter when you need a separate extracted result or more involved criteria arranged in a criteria range. Use a worksheet formula when you want a live result that updates as source data changes; the FILTER function is available in Microsoft 365 and Excel 2021 or later. A formula result is not the same as hiding source rows, and it may not suit a workflow that needs to act on the original records. For a one-off visual inspection, the ribbon’s Data > Filter command may be enough without VBA.
Choose based on the result you need: in-place visibility, copied records, an automatically updating output, or a reusable macro. Avoid deleting rows simply to imitate a filter unless permanent removal is truly intended; deletion changes the data and is much harder to reverse safely.
Frequently Asked Questions
Does VBA AutoFilter hide cells or whole rows?
It hides entire worksheet rows that do not meet the active criteria. The cells in those rows remain in the workbook and reappear when the filter is cleared.
What does Field:=1 mean in VBA AutoFilter?
It means the first column in the range passed to AutoFilter. If the range begins at column C, Field 1 refers to column C.
How do I filter for more than one value?
For two conditions, use Criteria1, Criteria2, and an operator such as xlOr. For a list of values, an array of criteria with xlFilterValues can be used.
How do I remove a VBA filter but keep the arrows?
When rows are actively filtered, call the worksheet’s ShowAllData method, preferably after checking FilterMode. This shows all records while leaving the filter controls available.
Can I run a VBA AutoFilter macro in Excel for the web?
No. Excel for the web can open and display a macro-enabled workbook, but VBA macros must be run in desktop Excel for Windows or Mac.
Related
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Formatting: Buttons, Icons, and Message TextIn VBA, format a MsgBox by building its prompt text with string concatenation and line-break constants, then set…
- VBA Find Last Row: Reliable Methods and ExamplesIn VBA, find the last row in a particular column with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. To find the last row…
- Excel VBA UserForm Tab Order: Set It CorrectlyTo set an Excel VBA UserForm’s tab order, open the form in the Visual Basic Editor, choose View…
- Excel VBA Range.Formula: How to Read and Set FormulasIn Excel VBA, use a range’s Formula property to read or write a worksheet formula in A1 notation.…
- Excel VBA Reference: Where to Find It and How to Use ItAn Excel VBA reference can mean either Microsoft’s documentation for Excel’s VBA object model or a library that…
- Excel VBA Find and Replace Exact Text in a ColumnTo replace an exact cell value in one Excel column, use VBA’s Range.Replace method with LookAt:=xlWhole. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.