To apply an AutoFilter with VBA in Excel for Microsoft 365 on Windows, call the AutoFilter method on the range that includes your header row and data. For example, dataRange.AutoFilter Field:=5, Criteria1:="Open" shows rows whose fifth column within that range contains “Open.”
What AutoFilter does in VBA
AutoFilter adds or uses drop-down controls in a table’s header row so you can display only records matching chosen criteria. It hides rows that do not match; it does not delete them or move matching records to another location. VBA can apply the filter, change its criteria, and show the hidden records again.
The method is useful when a workbook needs a repeatable view: for example, a button could show open orders, overdue tasks, or sales for one region. The worksheet still holds the full dataset, so formulas and other workbook features may continue to refer to rows that are currently hidden. A filtered view should not be mistaken for a separate, reduced copy of the data.
In Excel’s object model, the key call is Range.AutoFilter. Its Field argument identifies the column to filter, while Criteria1 and, where needed, Criteria2 specify what to show. The range should include the field names in its first row. If the data is an Excel Table, you can use the table’s range as the filter range.
Apply a basic filter to a worksheet range
Suppose a worksheet named Orders has headers in row 1, and the Status field is the fifth column of a contiguous dataset beginning in A1. This macro filters that field to show Open orders. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.
Sub FilterOrdersByStatus()
Dim ws As Worksheet
Dim dataRange As Range
' Use the block of data contiguous with the header in A1.
Set ws = ThisWorkbook.Worksheets("Orders")
Set dataRange = ws.Range("A1").CurrentRegion
' Field 5 means the fifth column inside dataRange.
dataRange.AutoFilter Field:=5, Criteria1:="Open"
End Sub
CurrentRegion selects the connected block around A1, stopping at blank rows or columns. It is convenient when the dataset is a solid rectangle, but it can select too little if an entire column or row is blank inside the data. It can also include adjacent content that you did not intend to filter. For a fixed layout, an explicit range such as ws.Range("A1:E500") may be more predictable. For a growing dataset, an Excel Table is usually easier to maintain.
The field number is relative to the left edge of the filter range, not the worksheet’s column letters. If the range is B1:F500, Field 1 means column B and Field 5 means column F. The first row of the range is treated as the header row; include it once, and do not start the filter range at the first record instead.
Choose criteria for text, numbers, and multiple values
Text criteria are commonly supplied as strings. A criterion such as "Open" matches that field value; spelling, spaces, and the actual contents of the cells matter. Wildcards can help with partial text matches: an asterisk represents any number of characters, and a question mark represents one character. For example, "=*east*" can match text containing “east.” If a value itself contains a wildcard character, Excel uses a tilde to escape it.
Numeric criteria can use comparison operators in the criterion string. For example, Criteria1:=">=100" asks for values at least 100. You can also use two criteria for a range of values, joined with xlAnd. For two alternative values in the same field, use xlOr:
Sub FilterOpenOrPending()
Dim ws As Worksheet
Dim dataRange As Range
' The range begins in A1 and Status is its fifth field.
Set ws = ThisWorkbook.Worksheets("Orders")
Set dataRange = ws.Range("A1").CurrentRegion
' Show rows matching either status.
dataRange.AutoFilter Field:=5, Criteria1:="Open", _
Operator:=xlOr, Criteria2:="Pending"
End Sub
For a numeric interval, the method call can use a lower and upper bound with Operator:=xlAnd, such as criteria for values greater than or equal to 100 and less than or equal to 500. Check that the field really contains numbers: values imported as text may not behave like numeric values when filtered.
Date filtering deserves extra care. Dates displayed in cells can be stored as Excel date serial numbers, while regional settings affect how date text is interpreted. If a date criterion works on one computer but not another, confirm that the cells contain real dates and use a criterion based on date values rather than an ambiguous text string. For date-group filters such as a particular month or year, the filter options and behavior can depend on how the dates are stored and on Excel’s filter interface.
Use AutoFilter with an Excel Table
Tables are often a better choice than manually maintained ranges because they expand when new rows are added and have named columns. The following example filters the Status column in a table named OrdersTable. In a table, the column index provides the field number used by AutoFilter.
Sub FilterOrdersTable()
Dim ws As Worksheet
Dim orders As ListObject
Dim statusField As Long
' Get the named table from its worksheet.
Set ws = ThisWorkbook.Worksheets("Orders")
Set orders = ws.ListObjects("OrdersTable")
statusField = orders.ListColumns("Status").Index
' Filter the table to show open orders.
orders.Range.AutoFilter Field:=statusField, Criteria1:="Open"
End Sub
The table and column names must match the workbook exactly. If the table is renamed, or if the header is changed from Status, the macro will need updating. Tables also make the intent clearer than a bare field number, which is helpful when someone later adds or rearranges columns. A table filter affects that table’s displayed rows, while a filter applied to a worksheet range is tied to the range supplied in the call.
Show all records and manage existing filters
To reveal rows hidden by active filters on a worksheet while keeping the filter controls available, use ShowAllData. This macro checks whether the worksheet is in a filtered state before calling it:
Sub ShowAllOrders()
Dim ws As Worksheet
' Save a copy before running macros; Ctrl+Z cannot undo VBA.
Set ws = ThisWorkbook.Worksheets("Orders")
' Remove active filter criteria and show the records again.
If ws.FilterMode Then
ws.ShowAllData
End If
End Sub
ShowAllData reveals records hidden by filters; it does not remove the header drop-down arrows. By contrast, AutoFilterMode reports whether AutoFilter arrows are displayed on the worksheet. These properties answer different questions. Setting AutoFilterMode to False removes the arrows and can affect a filter setup someone else is using, so avoid doing that casually in a shared workbook.
Filters belong to a particular range or table. Before changing a filter, check whether another macro or a user has already applied one to a different range on the same sheet. Reapplying AutoFilter to an unintended range can lead to confusing arrows or criteria. If your macro is meant to own the filter state, define the range or table explicitly and document that behavior.
Common problems and how to fix them
- Wrong column is filtered: Recount Field from the left edge of the supplied range. It is not necessarily the worksheet column number.
- Headers appear among the results: Make sure the filter range starts at the header row and that the first row contains field names.
- New rows are not included: A fixed range does not automatically grow. Use a table or calculate the range based on the data layout.
- The macro finds a smaller block than expected:
CurrentRegionstops at blank rows and columns. Inspect the selected range or use a clearly defined table. - No rows match: Check for leading or trailing spaces, different spellings, numbers stored as text, and criteria that do not match the cell values.
- A second filter behaves unexpectedly: Confirm that the new call uses the same intended range and field numbering as the existing filter.
- The macro errors while clearing filters: There may be no active filter, the sheet may be protected, or the current filter state may differ from what the code expects. Check the state before calling a method and review worksheet protection.
When diagnosing a filter, inspect the range and its headers in the Immediate Window or with a breakpoint, rather than assuming that the visible worksheet layout matches the macro’s assumptions. Also verify that there are no merged cells or blank header names in the filter range; a clean rectangular dataset with one header per column is easiest to filter reliably.
Windows, Mac, and Excel for the web
The examples here target Excel for Microsoft 365 on Windows. Microsoft documents the Range.AutoFilter method as unsupported on Excel for Mac, so do not assume that VBA using this method will run unchanged in a Mac workbook. If a macro must work on both platforms, check the object-model support for the specific Excel versions in use and test the exact filtering approach on each platform. On Mac, users can apply filters through the Excel interface using the Data tab’s Filter command, even when a Windows VBA implementation is not portable.
Excel for the web can display and interact with workbook filters, but it cannot run VBA macros. A user who needs the macro must open the workbook in the desktop Excel application. On Windows, keyboard shortcuts differ from Mac shortcuts, but the VBA statements shown above do not rely on a keyboard shortcut.
Saving, security, and practical safeguards
Workbooks containing VBA should be saved in a macro-enabled format such as .xlsm; saving as a standard .xlsx file does not preserve VBA code. Excel may also block macros based on file origin or the organization’s security settings. Enable macros only for workbooks you trust, and do not lower security settings just to run an unfamiliar file.
Before running a filter macro, save a separate copy of important workbooks. Although AutoFilter itself hides rather than deletes rows, a larger macro could make other changes, and Excel’s undo history cannot reverse VBA actions with Ctrl+Z. A safe routine is to confirm the worksheet and table names, run the macro on a copy, and verify both the visible results and the underlying records.
Frequently Asked Questions
How do I apply AutoFilter to a specific column in VBA?
Call AutoFilter on the full range including headers, then set Field to the column’s position within that range and provide a criterion. For example, Field 2 means the second column of the filter range, not necessarily worksheet column B.
Does VBA AutoFilter delete rows that do not match?
No. AutoFilter hides nonmatching rows in the worksheet view. To delete records, separate deletion code would be required, and that should be treated as a distinct, potentially destructive operation.
How can I clear a filter but keep the drop-down arrows?
If the worksheet is filtered, use ShowAllData to display all records again. The filter controls remain available. Check FilterMode first so the macro does not try to clear a filter when none is active.
Can I filter for two values in the same column?
Yes. Supply the first value as Criteria1, the second as Criteria2, and use Operator:=xlOr when either value should match. The example above shows Open or Pending orders.
Why does Field 1 filter the wrong column?
Field numbering starts at the leftmost column of the range passed to AutoFilter. If the range starts in column C, Field 1 refers to C, even though C is the worksheet’s third column.
Related
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Macro Recorder App: What It Does and How to ChooseA macro recorder app captures a sequence of actions so you can repeat them automatically; in desktop Excel…
- VBA Import CSV in Excel: Methods, Code, and FixesTo import a CSV file with VBA in Excel for Microsoft 365 on Windows, use Workbooks.OpenText to parse…
- Excel VBA Compile Error: Syntax Error Causes and FixesAn Excel VBA “Compile error: Syntax error” message means VBA cannot parse part of your code as valid…
- Excel VBA UserForm Dynamic List: Populate and UpdateTo create a dynamic list in an Excel VBA UserForm, clear a ListBox or ComboBox and refill it…
- VBA Find the Last Row With Data in an Excel WorksheetTo find the last row in a known column, start at the bottom of that column and use…
- VBA For Loop Backwards: Syntax, Examples, and Common FixesTo make a VBA For loop count backwards, give it a negative Step, such as For i =…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.