Excel Macro Filter: How to Automate Data Filtering

To filter a worksheet with a macro, use VBA’s AutoFilter method on a range that includes its header row, then specify the column and criterion—for example, filter the third column to show only rows marked “Open.” A macro can repeat that action on demand, but Excel’s ordinary Data > Filter command is often enough if you only need to filter manually.

What an Excel macro filter means

An “Excel macro filter” usually means a VBA macro that applies or changes a filter on a worksheet. The filtering itself is performed by Excel’s built-in AutoFilter feature; the macro automates the steps, such as selecting the data range, setting criteria, and optionally clearing an old filter before applying a new one.

For example, an order list might have columns for Order ID, Customer, Status, and Amount. A macro can filter Status to show only Open orders. The other rows are hidden from view, not deleted or moved, so clearing the filter makes them visible again. Filtering changes what is displayed; it does not, by itself, edit the underlying values.

This is useful for repeatable tasks: preparing a daily report, showing overdue items, or applying the same criteria to a regularly refreshed export. A macro is less helpful when you only need an occasional filter, because the built-in filter controls already let you choose values without writing code.

How VBA AutoFilter works

AutoFilter applies criteria to a range with column headings. The Field argument identifies a column by its position within that range—not by its worksheet letter. If the filtered range begins in column A, Field 3 is column C. If the range begins in column D, Field 3 is column F.

The first row of the range should contain headings, and each column should have a distinct, meaningful header. Excel adds filter arrows to those headings. Rows that do not meet the selected criterion are hidden while the filter is active. Sorting is a separate action: a filter narrows visible rows, while sorting changes their order.

A common range choice is Range("A1").CurrentRegion. It identifies the contiguous block of cells around A1, stopping at blank rows or columns. That is convenient for a simple, rectangular list, but a blank row inside the data can split the region and leave records outside the filter. For regularly maintained data, converting the list to an Excel Table with Insert > Table often provides a more dependable structure.

Prepare the worksheet and choose a range

Before writing the macro, check that the data is arranged as one list: a single header row followed by records, with one type of information per column. Avoid blank rows or columns inside the list, merged cells in the header, and repeated or empty headings. Those issues can make it harder to identify the intended filter range or field.

Decide whether the macro should filter the whole list or a defined range.
Identify the criterion and its column position inside that range.
Consider what should happen if another filter is already active.
Test the macro on a copy if the workbook contains important data or complex formatting.

If the list is an Excel Table, it already has built-in filter controls and expands as rows are added. A range-based macro can still filter a table, but a table-specific macro may be preferable when the workbook’s layout changes. The sample below intentionally assumes a simple contiguous range starting at A1 so its behavior is easy to follow.

Create and run a basic filtering macro

The following VBA example assumes a worksheet named Orders, headers in row 1, and a contiguous list starting in A1. The third column is Status, and the macro filters it to show rows whose value is exactly Open. If your sheet, starting cell, or column order differs, adjust those references before running it.

Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) so the VBA project can be retained. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the code. On Mac, Option+F11 opens the editor on many keyboards; some keyboards require Fn as well.

Sub FilterOpenOrders()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Use the worksheet and contiguous list that begin at A1.
    Set ws = ThisWorkbook.Worksheets("Orders")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Stop if the range has no data rows beneath its header.
    If dataRange.Rows.Count < 2 Then Exit Sub

    ' Clear active criteria before applying the requested filter.
    If ws.FilterMode Then ws.ShowAllData

    ' Field 3 means the third column within dataRange.
    dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

To run it, return to Excel, choose Developer > Macros, select FilterOpenOrders, and choose Run. If the Developer tab is hidden on Windows, enable it under File > Options > Customize Ribbon. On Mac, the ribbon can be customized in Excel’s settings. The macro leaves the filter arrows available and displays only records with Open in the third field. If there are three such records, those three data rows remain visible; the actual count depends on the workbook’s contents.

Change criteria and filter multiple values

For a different exact text value, change the criterion from Open to the value present in the cells, such as Pending. Text matching is based on the cell contents, so extra spaces or inconsistent labels can prevent the expected rows from appearing. If the column contains numbers or dates, use criteria appropriate to those values; Excel’s AutoFilter supports comparisons and date filtering as well as exact values.

To show more than one specific text value in the same field, VBA can use an array criterion with the xlFilterValues operator. For example, the AutoFilter call can be written as dataRange.AutoFilter Field:=3, Criteria1:=Array("Open", "Pending"), Operator:=xlFilterValues. That displays rows matching either listed value in that column. It does not mean “Open in one column and Pending in another”; criteria across different fields are combined as additional conditions.

For numeric ranges, date periods, or criteria that change each time the macro runs, decide how the macro will receive the values—such as from cells, a prompt, or a fixed rule—and validate them. A fixed criterion is easier to maintain but less flexible. A cell-driven criterion lets a user change the selection without editing VBA, provided the macro reads the correct cell and handles blank or invalid entries.

Clear filters without removing the filter controls

There is an important distinction between clearing filter criteria and turning off AutoFilter. In the sample, ws.ShowAllData displays all rows when a filter is active; the filter arrows remain. The FilterMode check prevents the macro from calling ShowAllData when no filtered view is active. This makes rerunning the sample practical when an earlier criterion has hidden rows.

To remove the arrows as well, turn off AutoFilter for the range, but that is not necessary for a routine that applies a new criterion. Excel’s Data > Clear command clears current filter criteria while preserving the controls. Data > Filter toggles filtering controls for the selected list. Choose deliberately: users often want to see all rows again, not remove the ability to filter.

Common problems and how to diagnose them

The wrong column is filtered: Field is relative to the range. Count from the first column in dataRange rather than using the worksheet column letter.
Some rows are missing: Check for blank rows or columns that break CurrentRegion, and verify that the range includes every record.
No rows match: Confirm the spelling, spaces, and data type in the criterion column. A number stored as text may behave differently from a numeric value.
The macro reports a worksheet error: Check that the tab is actually named Orders and that the workbook containing the code is the one referenced by ThisWorkbook.
ShowAllData causes trouble: It should only be called when a filtered view is active. The sample checks FilterMode first.
The macro appears unavailable: Confirm the file is saved as .xlsm and that macros are permitted under the organization’s security policy.

When debugging, step through the macro in the Visual Basic Editor and inspect the selected worksheet and range. Also check whether the list has a header row and whether the desired field is actually its third column. If the data is an Excel Table or multiple separate lists share the sheet, a hard-coded CurrentRegion may not identify the intended records.

Macro filtering versus built-in filters and formulas

Use Data > Filter when a person needs to choose criteria interactively and the task is occasional. It requires no code and makes the current selection visible. Use a macro when the same operation must be repeated, triggered by a button, or combined with other actions such as refreshing a report. VBA is not automatically better: it adds maintenance, security, and compatibility considerations.

Excel’s worksheet FILTER function is another distinct option in supported Excel versions. It returns matching data into a spill range rather than hiding nonmatching rows in the original list. That can be useful for a separate live report, while AutoFilter is suited to changing which rows are visible in place. A worksheet function is not a VBA macro, and the two approaches have different behavior when users edit or share the workbook.

Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. The VBA approach is for desktop Excel. Excel for Mac supports VBA, though menus, keyboard behavior, and some Windows-specific automation features may differ. If colleagues use different platforms, check the actual macro dependencies before making it part of a shared workflow.

Security, saving, and sharing a macro workbook

Macros can make changes beyond filtering, so only enable VBA in files from sources you trust. Do not lower macro security broadly just to run an unfamiliar file. On Windows, macro options are available under File > Options > Trust Center > Trust Center Settings > Macro Settings, although workplace policies may control those settings. A trusted or digitally signed macro is preferable to enabling unknown code.

Keep the workbook in .xlsm format if it must retain its VBA project. Saving it as an ordinary .xlsx file removes the macros. Before distributing the file, tell recipients what the macro does, which sheet and columns it expects, and how to run it. A short note can prevent users from assuming that hidden rows were deleted. Keep a clean source copy and test changes on sample data when you alter the range or criteria.

Frequently Asked Questions

Can an Excel macro filter by more than one column? Yes. Apply AutoFilter criteria to separate fields in the same range. Excel then shows rows that meet the criteria across those fields. Make sure each Field number is counted relative to the filtered range.

Does filtering with a macro delete hidden rows? No. AutoFilter hides rows that do not match; it does not delete them. Clearing the filter reveals them again, unless separate code has been written to delete data.

Can I run a VBA filter macro in Excel for the web? No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run the macro, or use a non-VBA method if the workflow must run in a browser.

Why does my macro filter the wrong column? The Field number starts at the first column of the range passed to AutoFilter. It is not necessarily the worksheet’s column number. Recheck the range’s starting point and count the target column from there.

Can I undo a macro with Ctrl+Z? No. Excel clears its undo history when VBA code runs, so Ctrl+Z cannot reverse the macro’s changes. Save a backup first, especially before running code that edits or deletes data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.