To filter a worksheet with an Excel VBA macro, define the full range including its header row, then call its AutoFilter method with a field number and a criterion. For example, dataRange.AutoFilter Field:=3, Criteria1:="East" shows rows whose third column in that range contains East; the complete example below adds a second condition and explains how to adapt it safely.
What Excel’s VBA filter does
Excel’s AutoFilter hides rows that do not match selected conditions and displays the matching rows in the original table. It does not remove or rearrange the underlying records, so clearing the filter brings the hidden rows back. This is useful for reviewing a subset of a larger list without making a separate copy of the data.
A filter range should have one header row followed by records, with a consistent column layout. For example, a sheet named Data could contain Order ID in column A, Date in B, Region in C, Product in D, and Sales in E, with headers in row 1 and records below. The range passed to AutoFilter must include the headers: Excel uses them to create the filter controls and to identify the columns.
The Field argument is a number relative to the left edge of the range, not necessarily the worksheet column number. If the range begins in column A, its third field is column C. If the range begins in column B, its third field is column D. This distinction is a common source of macros filtering the wrong column.
A complete macro for two conditions
This example filters the Data sheet to show records from the East region with sales greater than 1,000. It assumes headers are in row 1, the data begins in A2, column A is populated for every record, and the Region and Sales columns remain C and E. Change the sheet name, field numbers, or criteria to match your workbook.
Sub FilterOrders()
Dim ws As Worksheet
Dim dataRange As Range
Dim lastRow As Long
Dim lastColumn As Long
' Use the worksheet that contains the records.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last record using the consistently populated ID column.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
If lastRow < 2 Then
MsgBox "There are no data rows to filter."
Exit Sub
End If
' Include the header row and all used columns.
Set dataRange = ws.Range(ws.Cells(1, 1), _
ws.Cells(lastRow, lastColumn))
' Remove previous filters so this run starts with a clean view.
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Field numbers are counted from the first column of dataRange.
dataRange.AutoFilter Field:=3, Criteria1:="East"
dataRange.AutoFilter Field:=5, Criteria1:=">1000"
End Sub
The conditions are applied to the same range, so Excel combines them: a visible record must meet both the Region and Sales criteria. The code clears an earlier filter before applying its own. The row-count check avoids trying to filter a header-only range, while the last-column check includes the headers through the last nonempty cell in row 1.
Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even when a macro only changes the displayed filter state. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code in the file.
How to set up and run the macro
- Confirm the worksheet name, header row, and columns used by the code. Make sure the chosen key column has a value on every data row; otherwise, finding the last row from that column can stop too early.
- In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with
Alt+F11. If the Developer tab is visible, you can also choose Developer > Visual Basic. On a Mac, tryOption+F11; some keyboards requireFn+Option+F11. The Visual Basic Editor is also available from Excel’s Tools > Macro menu on supported Mac versions. - In the editor, choose Insert > Module, paste the code into the module, and return to Excel.
- Run the macro from the editor with the cursor inside
FilterOrdersby pressingF5, or use Developer > Macros, select the macro, and choose Run. The Mac keyboard shortcut for running code can vary with function-key settings, so the menu is a reliable alternative.
If the Developer tab is missing on Windows, turn it on through File > Options > Customize Ribbon and select Developer. On a Mac, the ribbon can be customized through Excel > Preferences (or Settings, depending on the release) > Ribbon & Toolbar. Menu labels can vary slightly between Microsoft 365 updates, but the Visual Basic Editor and macro dialog are the relevant tools.
Changing criteria and selecting multiple values
For a single exact text match, use a criterion such as Criteria1:="East". A numeric comparison uses a text criterion containing an operator, such as Criteria1:=">1000", Criteria1:="<=500", or Criteria1:="<>0". In a VBA string, text must be enclosed in straight quotation marks. If a criterion is stored in a variable, pass that variable instead of typing a fixed value.
To show more than one exact text value in one field, use an array with the filter-values operator. For example, when the data range starts in column A and Region is its third field, this statement shows East or West:
dataRange.AutoFilter Field:=3, _
Criteria1:=Array("East", "West"), _
Operator:=xlFilterValues
That is different from applying a filter on Region and another on Sales. Separate field filters are combined as AND conditions: a row must satisfy both. Multiple values supplied for the same field are alternatives, so a row can match either listed region. For a two-condition OR within a single field, AutoFilter also supports Criteria1, Operator:=xlOr, and Criteria2, but that pattern is not the same as the array used for several selected values.
Wildcards can help with text searches: an asterisk represents any number of characters, and a question mark represents one character. For instance, a criterion such as Criteria1:="North*" can match entries beginning with North. If the data contains literal wildcard characters, or a mix of text and numbers, test the condition on a small sample first; how Excel interprets a criterion depends on the cell values and field type.
Dynamic ranges, Excel tables, and field numbers
The sample calculates a range from the last populated cell in column A and the last used header in row 1. This is straightforward when column A is a reliable record identifier and the header row is complete. If column A can contain blanks, use a different consistently populated column to find the last row. A completely blank row inside the data can also make methods based on a contiguous block, such as CurrentRegion, stop at that gap.
For a well-formed Excel table, a table object can be a more dependable boundary than manually calculating the last row because the table expands as records are added. Tables have named columns, which can make code easier to maintain when someone inserts or moves worksheet columns. However, the AutoFilter field argument still refers to the position of a field in the filtered table or range; confirm that the field index matches the table’s column order. Avoid applying a second, overlapping filter range on top of an existing table filter.
If you want to use a range that does not begin at A1, adjust both the range boundaries and field numbers. For example, if the headers occupy B1:F1, the third field in that range is column D. Do not write Field:=4 just because the worksheet column letter is D: for this range it is field 3. This relative numbering makes a macro portable when the data block is moved, but only if its range is updated correctly.
Clearing a filter without removing data
To show all records while leaving the filter dropdown arrows in place, use ShowAllData only when a filter is actively hiding rows. Checking FilterMode first avoids calling it when there is no active filter. To remove the dropdown arrows as well, set the worksheet’s AutoFilterMode property to False. The main example performs both actions before applying its new filter, so it replaces an earlier filter rather than layering its results on an unknown starting state.
For a separate clear macro, use the following code. It restores the visible rows on the active filter and then removes the filter arrows from the worksheet:
Sub ClearWorksheetFilter()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Show all rows if a filter is currently hiding some.
If ws.FilterMode Then ws.ShowAllData
' Remove AutoFilter dropdown arrows from the worksheet.
If ws.AutoFilterMode Then ws.AutoFilterMode = False
End Sub
Clearing a filter does not restore rows that were manually hidden, deleted, or hidden by another feature. If a row remains missing, check whether it was hidden manually or whether another filter or outline is affecting the sheet.
Common errors and practical checks
- Wrong worksheet name:
Worksheets("Data")must match the tab name exactly. A missing or misspelled sheet produces a run-time error. If the macro is stored in a different workbook,ThisWorkbookmeans the workbook containing the code, not necessarily whichever workbook is active. - Wrong field number: Count from the first column in
dataRange, not from worksheet column A or the column letter. Confirm the range starts where you think it does. - Headers or data layout differ: Blank headers, merged cells, or extra title rows above the table can prevent Excel from recognizing the intended filter range. Keep one header row directly above the records.
- Last row is incomplete: If the key column contains blanks, the
End(xlUp)calculation may omit later records. Base the calculation on a column that is filled for every record, or use a table with a defined data body. - Dates do not match as expected: Dates can be stored as text or as actual Excel date values, and regional date formats can make typed criteria ambiguous. For date filtering, verify the cells are real dates and build criteria deliberately for the workbook’s date system rather than assuming displayed text is the stored value.
- Macro does not run: Check that the file is saved in a macro-enabled format and that your organization’s security policy permits the macro. Do not lower security settings or enable code from an unknown source just to make a downloaded workbook run.
Safety and alternatives to VBA
A macro can affect many rows quickly, so inspect its range and criteria before running it. Keep a backup, especially if the code will be expanded to edit or delete records. Excel macro security is designed to limit untrusted code; enable macros only for files and sources you trust, and follow workplace policies. A filter macro like the examples above changes which records are displayed, but other VBA instructions could change workbook contents.
If you only need to filter occasionally, Excel’s built-in controls may be simpler: select a cell in the list and choose Data > Filter, then use a header dropdown. For repeatable cleanup or combining data from sources, Power Query may suit the task better because its transformation steps can be refreshed. In Microsoft 365, the worksheet FILTER function can return matching rows into another area, but it is a formula-based result rather than an AutoFilter that hides rows in the original list. VBA is most useful when the same interactive filtering steps need to run on demand or as part of a larger macro.
Frequently Asked Questions
Does an Excel VBA filter delete rows?
No. AutoFilter hides rows that do not meet the criteria; it does not delete them. Clear the filter to display the hidden records again. A separate macro that deletes rows would be a different operation and should be backed up carefully.
Can I filter two columns at once with VBA?
Yes. Call AutoFilter on the same range once for each field, as in the example filtering Region and Sales. Excel displays rows meeting both field conditions. Use multiple values in one field when you want alternatives such as East or West.
Why does Field:=3 filter the wrong column?
Field numbers count from the first column of the range passed to AutoFilter. If the range begins in B, field 1 is B and field 3 is D. Recheck both the left edge of the range and the field index.
How do I filter all rows that contain part of a word?
Use a wildcard criterion such as Criteria1:="*blue*" to match text containing blue, subject to the field’s contents and Excel’s filter interpretation. Test it against representative values, including blanks and values with punctuation.
Can I use this macro in Excel for the web?
Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Use Excel desktop for Windows or Mac to work with VBA. The filter itself can still be used through the web app’s supported worksheet controls.
Related
- Excel Data Validation from a List: Setup and Troubleshooting
- Excel Named Ranges: How to Create and Use Them
- Excel Uppercase to Lowercase Shortcut: What Actually Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Subscript Out of Range: Causes and FixesExcel’s “Subscript out of range” message is VBA runtime error 9: a macro has asked for an item…
- Excel VBA Date Formatting: Examples and FixesTo format a date in Excel VBA, use Format$ when you need formatted text, or set a cell’s…
- Excel VBA Button: Add One to a Worksheet Step by StepTo insert a VBA button in an Excel worksheet, show the Developer tab, choose Developer > Insert >…
- VBA Dictionary Class Modules: A Practical How-ToTo use a class module with a VBA Dictionary, define a class to represent one record, create an…
- Excel VBA: Add Double Quotes to a String in VBA CodeTo add double quotes around a string in Excel VBA, concatenate a quote character before and after the…
- VBA Excel Offset: How to Select a Range ReliablyIn Excel VBA, use the Offset property on a Range to refer to cells a chosen number of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.