Excel VBA Sort and Filter: Practical Examples

To sort or filter Excel data with VBA, use the worksheet’s Sort object to rearrange rows and the AutoFilter method to show rows that meet criteria. For example, a macro can sort a table by its second column or display only records whose region is “West”; the examples below assume a header row and data on a worksheet named Data.

What Sorting and Filtering Do

Sorting changes the order of records in a range. A sort by date can place the oldest or newest records first, while a sort by sales can rank values from largest to smallest. The entire record row should move together: sorting only one column can separate values from the names, dates, or other details they belong with.

Filtering does not rearrange or delete records. It temporarily hides rows that do not match the selected criteria, leaving the matching rows visible. Clearing the filter reveals the hidden rows again. This distinction matters when a macro runs later steps: a filter changes which rows are visible, but it does not turn the filtered results into a separate, permanent data set.

In Microsoft 365 for Windows, you can also use the ribbon’s Data > Sort or Data > Filter commands without VBA. VBA is useful when the same operation must be repeated, triggered by a button, or included in a larger process such as preparing a report.

Prepare the Worksheet and Enable Macros

Use a simple rectangular data range with one header row, such as A1:D100. Each column should have a distinct heading, and each row should represent one complete record. Avoid blank rows inside the data, merged cells in the range, and inconsistent data types in a column you intend to sort or filter. A column containing dates stored partly as real dates and partly as text, for example, may not sort as expected.

For the examples, the worksheet is named Data, column A is populated for every record, column B contains the sort key, and column C contains the region. To add a macro in Excel for Microsoft 365 on Windows:

Save a working copy of the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
Open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module.
Run a macro from the editor with F5, or return to Excel and run it through Developer > Macros.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Developer > Visual Basic to open the editor; keyboard shortcuts can vary with the keyboard and function-key settings. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros, so use the desktop application.

Sort a Range with VBA

This macro sorts the full A:D data range by column B in ascending order. It finds the last used record in column A, so that column must contain a value on every data row. The header is excluded from the sort, and the other columns move with each record.

Sub SortDataByColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    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

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:D" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

To sort in descending order, change Order:=xlAscending to Order:=xlDescending. The key range starts at B2 because row 1 contains headings; the full range passed to .SetRange starts at A1 so that each record stays intact. If your data has additional columns, expand the set range accordingly. If column A can be blank in the last record, find the last row using a column that is reliably populated instead.

Filter Rows with AutoFilter

This example shows only rows where the third column of A:D, column C, equals West. The field number is relative to the range passed to AutoFilter, not the worksheet’s column number: for a range beginning in column A, field 3 means column C.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Do not apply a filter to a sheet with no data rows.
    If lastRow < 2 Then Exit Sub

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

    ' Clear existing criteria before applying this example filter.
    If ws.FilterMode Then ws.ShowAllData
    dataRange.AutoFilter Field:=3, Criteria1:="West"
End Sub

To remove the criteria and show every row again, use ws.ShowAllData when a filter is active. To remove the filter arrows as well, use dataRange.AutoFilter to toggle them off, taking care that the range is the same filtered range. In Excel, the ribbon equivalent is Data > Filter. Ctrl+Shift+L toggles filter arrows on Windows; on Mac, use Data > Filter if the shortcut differs on your keyboard.

Use Multiple and Numeric Criteria

AutoFilter can handle more than one kind of test. For example, a numeric column can be filtered to values greater than or equal to 500 with Criteria1:=">=500". A text criterion such as Criteria1:="*urgent*" matches cells containing “urgent”; the asterisk is a wildcard for any sequence of characters. A question mark matches one character, and a tilde can be used to search for a literal wildcard character.

To show two specific region values in the third field, use an array and the value-list operator:

dataRange.AutoFilter Field:=3, _
    Criteria1:=Array("West", "Central"), _
    Operator:=xlFilterValues

For an “and” test on two different columns, apply filters to both fields in turn, using the same range. Excel then displays rows that satisfy both active field filters. Date criteria can be more delicate because the cells should contain genuine Excel dates, not text that merely looks like a date. For complex date groupings or advanced criteria, test the macro on a copy and confirm the visible results before relying on them.

Sort and Filter Excel Tables

Excel Tables, created with Insert > Table, often make automation more resilient than a fixed range. A table expands when rows are added, carries its header and filter controls, and can be addressed by its name and column headings. This avoids hard-coding a range such as A1:D100 when the number of records changes.

For a table named SalesTable, a sort key can be based on its named column rather than a worksheet address. In VBA, the table is represented by a ListObject; its Sort and Range members can be used to sort or filter its records. Check the table name in Table Design > Table Name before writing code. Table-based code is especially helpful when users append records, but it still depends on consistent headers and on the table containing the intended data.

Common Errors and Troubleshooting

Wrong field number: AutoFilter’s Field argument counts from the left edge of its range. If the range begins at B, Field 1 means worksheet column B.
Only one column moves: The sort’s SetRange must include every column belonging to each record, not just the sort key.
Header gets sorted into the data: Set .Header = xlYes when the range has a header. If there is no header, use xlNo.
Last rows are missed: The example detects the last row from column A. Choose a column that is filled in every record, or use a Table that expands with its data.
Results look out of order: Check for numbers or dates stored as text, leading or trailing spaces, and inconsistent values. A sort follows the cell values and types Excel recognizes.
ShowAllData causes an error: It is only appropriate when an active filter has hidden rows. Check ws.FilterMode before calling it, as the example does.

Also check whether the worksheet is protected, whether the macro refers to the correct workbook and sheet, and whether another filter is active on a different range. If the code uses ThisWorkbook, it targets the workbook containing the VBA code. That can differ from ActiveWorkbook, the workbook currently selected in Excel.

Safety, Undo, and Platform Notes

Save a copy of the workbook before running a macro that sorts or filters data. VBA actions generally cannot be reversed with Ctrl+Z, and a sort can change the row order that a user was relying on. If row order matters, add a stable ID or original-order column before sorting so the initial sequence can be restored.

Only enable macros in files from sources you trust. Excel’s macro security settings can block code, and a workbook may need to be saved as .xlsm to retain VBA. Standard range sorting and AutoFilter are available in desktop Excel for Windows and Mac, but ribbon layouts, keyboard shortcuts, and some VBA capabilities can differ. Macros do not run in Excel for the web; a desktop version is required to execute these examples.

Frequently Asked Questions

Can VBA sort by more than one column?
Yes. Add multiple sort fields to the worksheet’s SortFields collection before applying the sort. Add the primary key first, followed by the secondary key, and include the full record range in .SetRange. For example, sort by region first and then by date within each region.

Does filtering delete the rows that do not match?
No. AutoFilter hides nonmatching rows; it does not delete them. Clear the filter to display all rows again. If a later macro should process only visible rows, it must deliberately account for hidden rows rather than assuming they have been removed.

Why does AutoFilter Field 3 not refer to worksheet column C?
The field number is counted from the first column of the AutoFilter range. It refers to column C only when that range begins in column A. If the range begins in column B, Field 3 refers to worksheet column D.

Can I run these macros in Excel online?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the code. For web-based automation, Office Scripts are a separate option and use TypeScript rather than VBA.

How do I clear a filter without removing the arrows?
When the filter is active, call ShowAllData on the worksheet to reveal all rows while leaving the filter controls in place. Check that FilterMode is true before calling it, because there may be no active criteria to clear.

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.