VBA Range Filter: How to Use AutoFilter

To filter a worksheet range with VBA in Excel for Microsoft 365 on Windows, call the range’s AutoFilter method and specify a field number and criterion. For example, if headers are in A1:D1 and sales amounts are in column C, dataRange.AutoFilter Field:=3, Criteria1:=">=1000" displays rows whose sales amount is at least 1,000; it hides nonmatching rows rather than deleting them.

What a VBA range filter does

Excel’s AutoFilter adds filter controls to a header row and shows only records that meet the selected conditions. In VBA, the Range.AutoFilter method applies or changes those conditions. The range should normally include the column headings and all the records you want to filter. Filtering changes what is visible on the sheet; it does not remove the hidden records or alter their values.

The key detail is that a filter’s field number is counted from the left edge of the range, not from the worksheet’s column A. If the filtered range is A1:D200, column A is field 1 and column C is field 3. If the range instead starts at B1, worksheet column C is field 2. Getting this relationship right prevents a common mistake: filtering the wrong column even though the criterion itself is correct.

AutoFilter is useful for reviewing lists, preparing visible records for a report, or narrowing a dataset before a later operation. It is not the same as deleting rows, and it does not create a separate filtered copy. If you need to preserve the original layout while producing a distinct result set, consider copying visible rows to another sheet or using an Excel formula instead.

Prepare the range and its headers

Before applying a filter, make sure the selected range is a single rectangular block with one header row. Each heading should be present and reasonably distinct, such as “Region,” “Sales,” or “Order Date.” A blank heading or an extra title row can lead Excel to treat the wrong row as the header, making the filter controls confusing.

For a fixed dataset, you can name the range directly. For a list that grows over time, calculate its last row or convert it to an Excel Table. The example below assumes a worksheet named Sales, headings in A1:D1, and data beneath them. It finds the last used row by looking in column A, so that column should contain a value on every record. If it does not, choose a column that is consistently populated.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even when a macro only changes which rows are visible.

Apply a basic filter with VBA

This macro filters the third field in the A:D range, which is column C, for values of 1,000 or more. The range includes the header row, and Header:=xlYes tells Excel to treat its first row as headings. The test for fewer than two rows avoids trying to filter a header when there are no records.

Sub FilterHighValueSales()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim lastRow As Long

    ' Find the worksheet and the last record in column A.
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if the sheet contains only a header or is empty.
    If lastRow < 2 Then Exit Sub

    ' Include the headings and all records in columns A through D.
    Set dataRange = ws.Range("A1:D" & lastRow)

    ' Field 3 is column C within this range.
    dataRange.AutoFilter Field:=3, Criteria1:=">=1000", Header:=xlYes
End Sub

For instance, if C2:C5 contains 750, 1,000, 1,250, and 900, the filter leaves the records with 1,000 and 1,250 visible. Excel interprets the criterion as a comparison, so the greater-than-or-equal operator is part of the criterion string. Numeric criteria should be compared with the values in the worksheet, not with formatted display text such as a currency symbol added by cell formatting.

Choose the field and criteria correctly

The Field argument identifies which column of the filtered range to test. A range A1:D200 has four fields; a range C1:F200 also has four fields, but its first field is worksheet column C. When you change the range’s left edge, review every field number in the macro.

Criteria1 sets the main condition. A text criterion such as "East" matches that item, while comparison criteria can use operators such as >, >=, <, and <>. The last of these means “not equal to.” For a text prefix, a wildcard can be used; for example, a criterion of "North*" matches text beginning with North. The asterisk represents any number of characters, and a question mark represents one character.

Be mindful of data types. A column containing numbers stored as text may not behave like a clean numeric column. Likewise, dates should be real Excel date values rather than inconsistent text strings if you need dependable date comparisons. Check the source data before trying to compensate with a complicated criterion.

Filter on more than one condition

You can apply filters to multiple fields by calling AutoFilter on the same range more than once, using a different field number for each column. For example, filtering field 1 for “East” and field 3 for values at least 1,000 leaves rows that satisfy both conditions: the record must be in East and its amount must meet the threshold. Filters on separate fields work together as an AND condition.

For alternative values in one field, AutoFilter supports an OR condition through its operator argument. A criterion can also select multiple exact items using an array and the xlFilterValues operator. This is useful for choosing several regions without writing a separate filter for each one. Keep the criteria aligned with the actual cell values; extra spaces or inconsistent labels can make an apparently correct selection exclude records.

More complex logic, such as “Region is East and amount is high, or Region is West and amount is low,” may not fit neatly into simple field criteria. For that kind of condition, add a helper column that evaluates the rule, filter the helper column, or use a more explicit VBA loop. A helper column is often easier for another person to inspect and maintain.

Use a Table or a changing data range

An Excel Table is often the most reliable structure for data that grows. Tables expand as records are added, keep headers associated with the data, and provide filter controls automatically. In VBA, a table is represented by a ListObject. Referencing the table by its name is generally clearer than rebuilding a worksheet range from row numbers every time the macro runs.

A manually calculated range can still be appropriate for a simple, fixed report. Its main risk is choosing the wrong boundary: a hard-coded final row misses new records, while an overly broad range can include blank rows or unrelated content. Using CurrentRegion can be convenient when the data is a contiguous block, but a completely blank row or column can split the region. Choose the range method based on how the sheet is maintained, not just on which expression is shortest.

Work with dates and wildcard matches

Date filters deserve particular care because Excel stores dates as serial values and displays them using number formats. A date that looks like “1/15/2026” may be a true date value, or it may be text. If the column mixes both, a filter can produce surprising results. Standardize the column first, then test the macro against dates near the boundaries you care about.

For a recurring date range, such as orders within a month, consider whether the workbook’s date criteria need to account for regional date settings. A criterion written as a date string can be interpreted differently depending on the system’s locale. For robust automation, construct criteria with care for the workbook and Excel environment, and verify the visible records against a small known sample before using the macro on a critical report.

Wildcard matching is handy for variable text, but a wildcard has a special meaning. If the data itself contains an asterisk or question mark that you want to match literally, escape the character with a tilde. Also remember that a contains-style search and a starts-with search are different: *North* can match North anywhere in a label, while North* only matches labels that begin with it.

Clear a filter and handle visible rows

Applying a new criterion to a field changes that field’s current filter; other fields can remain filtered. To show every record again while leaving the filter controls available, check whether the worksheet is in filter mode and use ShowAllData. To remove the filter arrows as well, turn off AutoFilter for the range or use the worksheet’s filter controls. Showing all data and removing the controls are different actions.

Hidden rows still exist in the range. If a later macro should process only the visible records, it must deliberately select visible cells rather than assume every row in the range is displayed. This distinction matters when copying, totaling, or exporting filtered data: an operation over the entire range may include hidden records unless it is designed to work with visible cells only. Review the target range and the intended behavior before adding a destructive step such as deleting rows.

Common problems and practical checks

The wrong column is filtered: Recount the field from the first column of the range, not from worksheet column A.
No rows appear: Check for extra spaces, mismatched text, numbers stored as text, and an incorrect header or range boundary.
The macro misses new records: Replace a fixed endpoint with a reliable last-row calculation or use a Table that expands with its data.
The header is treated as a record: Include the header row and set the header option appropriately. If the range has no headers, do not tell Excel that its first row is a heading.
Existing criteria remain active: Remember that filtering one field does not necessarily clear conditions on other fields. Clear filters when the macro needs to start from a known state.
The macro reports that the worksheet name is invalid: Confirm the exact sheet tab name and that the workbook containing the code is the one referenced by ThisWorkbook.

Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros. On Mac, the worksheet’s filtering interface is available, but Microsoft’s VBA documentation marks the Range.AutoFilter method as unsupported on Excel for Mac. Do not assume a Windows macro using this method will work unchanged there; check the target Excel for Mac version and use an appropriate cross-platform approach if Mac execution is required.

Frequently Asked Questions

How do I filter a range by text in VBA?
Include the header row in the range, then call AutoFilter with the field number and the exact text criterion. The field number starts at the left edge of that range.

Does VBA AutoFilter delete rows?
No. It hides records that do not match the criteria. The underlying data remains in the worksheet and can be shown again by clearing the filter.

How do I filter two columns at once?
Apply a filter to each field on the same range. Conditions across different fields work together, so a row must meet both conditions to remain visible.

How do I remove all filter criteria but keep the arrows?
Use the worksheet’s Show All command or VBA’s ShowAllData when the sheet is currently filtered. This restores the rows without necessarily removing the filter controls.

Can I use Range.AutoFilter in Excel for Mac?
Microsoft documents this VBA method as unsupported on Excel for Mac. Test the exact target environment or choose a different approach for macros that must run on Mac.

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.