excelavon

VBA Filter and Sort in Excel: Code and Key Details

VBA & macros · Updated November 3, 2026

VBA Filter and Sort in Excel: Code and Key Details

To filter and sort a worksheet with VBA, use the AutoFilter method to show matching records and the worksheet’s Sort object to order them. The example below filters a table to show rows for West, then sorts its Sales column from largest to smallest; it assumes headers are in row 1 and the data occupies columns A:D.

What filtering and sorting do in VBA

Filtering hides rows that do not match criteria; it does not delete those records. Sorting rearranges records according to one or more columns. In Excel’s interface, you can access these tools from Data > Filter and Data > Sort. VBA automates the same kinds of operations, which is useful when a report needs to be refreshed consistently or when a macro performs several preparation steps.

In an AutoFilter range, the Field number is relative to the left edge of that range, not the worksheet’s column number. If the range starts in column A, Field 3 means column C. If the range starts in column B, Field 3 means column D. This distinction is a frequent source of filters being applied to the wrong column.

Example: filter West and sort Sales descending

In this example, columns A:D contain Customer, Date, Region, and Sales, respectively. The macro uses the worksheet named Data, reads the last used row in column A, filters Region to West, and sorts the list by Sales in descending order.

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

    ' Change Data if your worksheet has a different name.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last record, using column A as the continuous key column.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No data rows were found."
        Exit Sub
    End If

    Set dataRange = ws.Range("A1:D" & lastRow)

    ' Remove existing filter arrows, then apply a fresh filter.
    ws.AutoFilterMode = False
    dataRange.AutoFilter Field:=3, Criteria1:="West"

    ' Sort the full list by Sales, largest to smallest.
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("D2:D" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        .SetRange dataRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup gives you a reliable way to recover the original order or filter state. Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code in the file.

How to run the macro

  1. Check that row 1 contains the four headers and that every record uses the same columns. Change the worksheet name, range, and criteria in the code if your layout differs.
  2. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure, and return to Excel.
  3. Run the macro from the Visual Basic Editor, or use Developer > Macros in Excel and select FilterWestAndSortSales. If the Developer tab is hidden, enable it in Excel Options under Customize Ribbon.

Excel for Mac also supports VBA, though keyboard-key combinations can depend on the Mac keyboard and function-key settings. You can open the Visual Basic Editor from the Developer tab or Excel’s Tools > Macro menu. The ribbon and VBA object model are broadly similar, but some features and controls available to Windows macros are not available on Mac. Excel for the web does not run VBA macros; open the workbook in the desktop app to execute this code.

Changing the filter criteria and range

Update Criteria1 to match the value you want. For example, Criteria1:="East" filters for East. Text matching is generally not case-sensitive in Excel’s AutoFilter, and criteria usually need to match the data values rather than the column heading. A numeric criterion can be supplied as a number or as a comparison string, such as Criteria1:">>100" in VBA syntax (the actual criterion text is ">100"); use a single greater-than sign inside the string.

For clarity, a valid VBA line for values above 100 is dataRange.AutoFilter Field:=4, Criteria1:=">100". Here Field 4 means column D because the range begins in column A. Wildcards can help with text: Criteria1:="North*" matches text beginning with North, while Criteria1:="*West*" matches text containing West. An asterisk represents any number of characters and a question mark represents one character.

The sample identifies the final row by looking up column A. That works when each data row has a value in A and there are no gaps that make the last-row calculation misleading. If another column is always filled instead, use that column in the Cells expression. The range A1:D plus the last row must include all fields belonging to each record; leaving out a column can cause rows to become misaligned when sorted.

Sorting in ascending order or by multiple columns

To sort Sales from smallest to largest, change Order:=xlDescending to Order:=xlAscending. The sort key should cover data cells, not the header, while .SetRange should include the complete table including its header. Setting .Header = xlYes tells Excel not to treat the first row as a record. If there are no headers, use xlNo and adjust the range and key accordingly.

For a secondary sort, add another sort field before .SetRange. For example, sort first by Region and then by Sales within each region by adding a Region key first and a Sales key second:

.SortFields.Clear
.SortFields.Add Key:=ws.Range("C2:C" & lastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("D2:D" & lastRow), _
    SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal

These lines belong inside the existing With ws.Sort block and replace its original single .SortFields.Add call. The first field is the primary order; later fields break ties within earlier values. Keep the existing .SetRange, header, orientation, and .Apply settings.

Using AutoFilter with Excel tables

For data formatted as an Excel table, VBA can refer to the table by its ListObject name rather than rebuilding a range from row numbers. Tables expand when records are added and make column references easier to recognize. For example, if a table is named SalesTable, its Region filter can be applied with SalesTable.Range.AutoFilter Field:=3, Criteria1:="West", provided Region is the third table column.

Tables also have their own sort object, so a table-based macro can sort its DataBodyRange or use the table’s Sort settings. This approach is often preferable for reports that grow over time. The exact table name is shown under Table Design when a cell inside the table is selected; table column order, not worksheet letter, determines the AutoFilter field number.

Common problems and useful checks

  • Wrong field filtered: Count columns from the left edge of the range. Field 1 is the first range column, whether it is A or another worksheet column.
  • Header moved into the records: Confirm .Header = xlYes and that the range begins on the actual header row.
  • Sort mixes unrelated data: Include every column of each record in .SetRange. Sorting only the Sales cells can separate sales figures from their customers and regions.
  • Records are missed: Check that the last-row key column is populated throughout the list and that the range reaches the final record. Blank rows and inconsistent layouts may require a different way of finding the data boundary.
  • Filter arrows disappear: The sample sets ws.AutoFilterMode = False before creating its filter, which removes any existing arrows on that worksheet. Omit that line if you need to preserve an unrelated filter setup, and make sure the intended range is the one being filtered.
  • Macro does not run: Confirm the workbook is open in desktop Excel, macros are permitted by the organization’s security settings, and the worksheet name in the code exists exactly as written.

Applying an AutoFilter does not itself remove data, but the sample deliberately replaces existing filter arrows on the worksheet. Sorting changes row order, and a macro cannot be undone using Ctrl+Z. Keep a backup, especially when a workbook contains formulas, manually hidden rows, or a carefully arranged report.

Frequently Asked Questions

Can VBA filter and sort at the same time?

Yes. Apply an AutoFilter to the data range and then configure and apply a sort, as the example does. Filtering controls which records are displayed; sorting controls their order. Neither operation requires a separate worksheet or a helper column for a basic list.

Does AutoFilter delete the rows that do not match?

No. AutoFilter hides nonmatching rows so they can be shown again by clearing the filter. Do not confuse filtering with deleting rows or copying visible records to another location; those are separate operations.

How do I clear a VBA filter?

If a filter is active and has hidden records, a common worksheet-level check is If ws.FilterMode Then ws.ShowAllData. This shows all filtered records while leaving the filter dropdowns available. If you also want to remove the dropdown arrows, set ws.AutoFilterMode = False; that removes the worksheet’s AutoFilter interface rather than just showing all records.

Can I filter for more than one value?

Yes. For two exact text values in the same field, AutoFilter can use an array of criteria with the xlFilterValues operator. For more complex combinations, such as different conditions across columns, apply filters to separate fields or use the Advanced Filter feature. Choose criteria based on whether the conditions should be combined as AND or OR.

Will this macro work in Excel for the web?

No. Excel for the web can display and edit many macro-enabled workbooks, but it cannot run VBA code. Use the desktop version of Excel for Windows or Mac to run the macro; the Windows-specific editor steps and shortcuts may differ on a Mac.

Related

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.