To filter and sort rows with VBA, apply an AutoFilter to the data range, then use the worksheet’s Sort object to set a sort key and apply it. For example, filter column B for “East” and sort column C from largest to smallest; the sample below shows the complete pattern for Excel for Microsoft 365 on Windows.
What VBA AutoFilter and Sort Do
AutoFilter hides rows that do not meet specified criteria; it does not delete those records. Sorting rearranges records according to values in one or more columns. In a typical macro, you apply a filter and a sort to the same rectangular range so the column headings and associated data stay together.
These are separate operations in VBA. The Range.AutoFilter method sets the filter criteria, while the worksheet’s Sort object defines and applies the sort. A filter does not automatically sort its results, and a sort does not choose which records should be visible. Combining the two is useful for reports, recurring data cleanup, and buttons that display a particular subset in a predictable order.
Filtering is also different from deleting or copying rows. A filtered-out record remains in the source range and can reappear when the filter changes or is cleared. If a macro needs to create a separate report containing only matching records, it must explicitly copy the visible results to another range or worksheet.
A Basic VBA Example
Assume the worksheet named Data has headers in A1:D1, records in rows 2 through 100, region names in column B, and sales values in column C. The macro below shows East records and sorts the data by sales in descending order. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Sub FilterEastAndSortSales()
Dim ws As Worksheet
Dim dataRange As Range
' Use the worksheet and range that contain the headers and records.
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
' Clear any existing criteria without removing the filter arrows.
If ws.FilterMode Then ws.ShowAllData
' Field 2 means the second column within dataRange: column B.
dataRange.AutoFilter Field:=2, Criteria1:="East"
' Sort the range by sales in column C, largest to smallest.
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("C2:C100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange dataRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
The example assumes that the records really end at row 100 and that there are no unrelated cells inside A1:D100. Change the worksheet name, range, field number, criterion, and sort key to match the workbook. The filter criterion is exact text in this example; criteria can also use comparison operators, wildcards, or multiple criteria where the data and intended result call for them.
How Field Numbers and Sort Keys Work
The Field argument is a position within the range on which AutoFilter is called, not necessarily the worksheet’s column number. If the range is A1:D100, Field 1 refers to A, Field 2 to B, and Field 3 to C. If the range starts at C1, Field 1 refers to worksheet column C. This is a common cause of macros filtering the wrong column.
The sort key, by contrast, is supplied as a worksheet range. In the example, C2:C100 contains the values used to order the records. The .SetRange line tells Excel which full range of records should move together; do not set it to only the key column, or other columns may no longer stay aligned with their original records. .Header = xlYes tells Excel that the first row in the sort range contains headings rather than data.
For a list beginning in A1, worksheet column numbers and filter field numbers happen to match. For a list beginning elsewhere, calculate the field position from the list’s left edge. For example, column E is Field 3 in a range starting at C, because C, D, and E are the first, second, and third columns of that range.
Choosing a Reliable Data Range
A fixed range such as A1:D100 is easy to understand, but it stops including new records added below row 100. One option is to find the last used row and build the range at run time. This approach assumes column A is populated on every record row and that row 1 contains the headers:
Sub FilterAndSortDynamicRange()
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
' Stop if the header is the only nonblank cell in the key column.
If lastRow < 2 Then Exit Sub
Set dataRange = ws.Range("A1:D" & lastRow)
If ws.FilterMode Then ws.ShowAllData
dataRange.AutoFilter Field:=2, Criteria1:="East"
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("C2:C" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange dataRange
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
End Sub
The last-row method can miss records if column A has blanks in the middle or is not filled for every record. In that case, use a column that is reliably populated, calculate the last row using a different dependable method, or convert the data to an Excel table. A table, created with Insert > Table, expands as records are added and provides a named data body that is generally easier to maintain than hard-coded row limits.
Sorting by More Than One Column
To sort by multiple criteria, add more sort fields before applying the sort. The first key is the primary ordering; later keys break ties. For example, sort by region A to Z, then by sales largest to smallest within each region. Add both keys to the same SortFields collection, set the full data range once, and apply once. Each key’s range should cover the data rows, not the header.
For text, use xlAscending or xlDescending. For numbers and dates, the same order constants apply: ascending puts smaller or earlier values first, while descending puts larger or later values first. If the sort must follow a nonalphabetical sequence, such as High, Medium, Low, a custom list may be more suitable than a standard A-to-Z sort. Confirm the intended order in Excel before relying on it in a repeated report.
When sorting dates or numbers produces an unexpected result, check the underlying values. Text that looks like a date or number may sort differently from genuine date serial values or numbers. Applying a number format alone changes appearance, not the stored value or its type.
Using AutoFilter with Excel Tables
For a regularly updated dataset, an Excel table is often a better foundation than a fixed range. Tables have header names, expand when new rows are entered directly below them, and expose their filter controls automatically. In VBA, a table is represented by a ListObject. Its Range includes the header, while its DataBodyRange contains data rows only and is Nothing when the table has no data rows.
Table-based code can refer to columns by header name rather than relying on worksheet positions. This makes a macro easier to maintain when the table’s columns move, provided the header text remains unchanged. It also avoids guessing the final data row. However, table column names must be spelled exactly as they appear, and a table with no records needs to be handled before code tries to use its data body.
Tables are especially useful when users append records over time or when other formulas and reports refer to the same dataset. A simple range can still be appropriate for a one-off worksheet or a stable, controlled layout. Whichever approach you choose, define one clear data boundary and ensure the sort range includes every field that belongs to each record.
Common Problems and How to Fix Them
- Wrong column is filtered: Recount Field from the left edge of the AutoFilter range. Field 1 means the first column in that range, not always worksheet column A.
- Rows become mismatched: Set the sort range to the complete table or record range, not only the sort-key column. Include every related field in each row.
- The macro errors when clearing filters:
ShowAllDatais only appropriate when a filter is active. CheckFilterModefirst, as in the examples.AutoFilterModeindicates whether filter arrows are displayed; it is not the same as having active criteria. - New rows are ignored: Replace a stale fixed range with a reliable dynamic range or an Excel table. Verify that the column used to find the last row is populated on every record.
- The header is sorted into the records: Set
.Header = xlYeswhen the first row of the sort range contains headings. Check that the range begins on the actual header row. - Blank-looking values sort strangely: Check for spaces, text-formatted numbers, and inconsistent date values. A cell that appears empty may contain a formula returning an empty string or other content.
Also check that the sort key belongs to the range being sorted and that the worksheet is not protected in a way that blocks filtering or sorting. If the macro applies a new filter but old criteria remain on other columns, clear the existing criteria deliberately before setting the new ones; do not assume that changing one field removes every other filter.
Running VBA on Windows, Mac, and the Web
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and paste in the macro. Run it with the cursor inside the procedure and press F5, or run it from Developer > Macros. If the Developer tab is not visible, enable it in File > Options > Customize Ribbon. Save a macro-enabled workbook as an .xlsm file if the code needs to be retained.
Excel for Mac also supports VBA macros, though menu labels and keyboard behavior can vary by keyboard and Excel release. Option+F11 opens the Visual Basic Editor on many Mac setups; the Fn key may also be needed. You can use the Mac Excel menus to open the editor or run macros if that shortcut is unavailable. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use desktop Excel to execute this code.
Only enable macros in workbooks you trust. A macro can change or remove data, and Excel does not provide Ctrl+Z undo for VBA actions. Keep a backup and review what the code targets before running it, especially when adapting a sample to a workbook with important records.
Frequently Asked Questions
Can I filter and sort in one VBA macro?
Yes. Apply Range.AutoFilter to set criteria, then configure the worksheet’s Sort object and call .Apply. The operations are separate, so include both when you need both outcomes.
Does AutoFilter delete rows that do not match?
No. AutoFilter hides nonmatching rows; the records remain in the range. Clear the filter to display them again, or copy visible records elsewhere if you need a separate extracted list.
How do I sort the filtered results only?
Use a sort range that covers the complete dataset and apply the sort after setting the filter. Excel’s normal sorting of a filtered list orders the visible records while filtered-out records remain hidden. Check the resulting view and test on a copy if the workbook’s layout or hidden rows are important.
What does Field:=2 mean in AutoFilter?
It means the second column inside the range passed to AutoFilter. If that range starts at A, it means B; if it starts at C, it means D.
Why does my sort put numbers or dates in the wrong order?
Some values may be stored as text rather than as numbers or dates, or may contain spaces and inconsistent entries. Check the cell values and data types, not just their displayed number format.
Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Windows or Mac desktop application to use VBA.
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Resize Pictures to Fit in Excel Cells AutomaticallyTo resize pictures to fit inside Excel cells with VBA, run a macro that finds each picture, scales…
- VBA Sort Syntax: Examples for Excel RangesIn Excel VBA, the basic sort syntax is Range.Sort: specify the range to rearrange, a key column, its…
- Excel VBA: Copy and Paste Range ValuesTo copy a range’s results without its formulas or formatting, assign the source range’s Value property to a…
- VBA Copy and Paste Data Between Excel SheetsTo copy a range from one Excel worksheet to another with VBA, use a macro that names both…
- VBA Sort Cells in Excel: Code, Options, and Common FixesTo sort cells with VBA in Excel, sort the entire data range by a key column—not just one…
- Excel VBA: Find the First Row Above a Value ThresholdTo find the first row where a cell’s value is greater than a threshold, loop through the relevant…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.