To filter and sort rows with Excel VBA, apply AutoFilter to the data range, then configure that range’s Sort object. For example, filter a Status column to show “Open,” then sort the records by that column or another field; filtering chooses which rows appear, while sorting controls their order.
How AutoFilter and sorting work together
AutoFilter and Sort are related but separate operations. AutoFilter adds dropdown arrows to a header row and hides rows that do not match its criteria. A sort rearranges records according to one or more keys, such as date, name, or status. Adding a filter does not automatically sort the data, and applying a sort does not decide which records are visible.
In VBA, a range’s AutoFilter method identifies the field by its position within the filtered range. If the range is A1:C100, then Field:=1 refers to column A, Field:=2 to B, and Field:=3 to C. These are relative positions, not worksheet column numbers. A separate Sort object specifies the full range to sort and the cells that form the sort key.
When a filter is active, Excel displays matching rows and hides nonmatching rows. Sorting a filtered list is useful for arranging the visible results, but it is not the same as clearing the filter and sorting every record. If the final order of hidden records matters too, clear the filter, sort the full data range, and then reapply the filter.
Prepare the data range
Start with a rectangular list: one header row followed by records, with a field in each column and no completely blank rows splitting the list. For the example below, assume a worksheet named Data has headers in A1:C1—Order, Status, and Date—and records below them. The macro finds the final used row in column A, filters Status for Open, and sorts the specified list by Status in ascending order.
Before running VBA, save a copy of the workbook. Macros can change data and cannot be undone with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its VBA code.
Example VBA macro: filter, then sort
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in the procedure. Change the worksheet name, range columns, criteria, or sort key to match the workbook. On Mac, open the editor through Developer > Visual Basic; the keyboard shortcut can vary by keyboard and may require Option+F11 or the Fn key. Excel for the web does not run VBA macros.
Sub FilterAndSortOpenOrders()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
' Use the worksheet that contains the list.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last record using the list's first column.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set dataRange = ws.Range("A1:C" & lastRow)
' Remove any existing filter and its criteria.
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Field 2 is the second column in dataRange: Status.
dataRange.AutoFilter Field:=2, Criteria1:="Open"
' Sort the list by Status, keeping the header in place.
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange dataRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Run it from the Visual Basic Editor with the cursor inside the procedure and press F5, or run it from Excel’s Developer > Macros dialog. The macro removes any existing AutoFilter first, so it replaces earlier filter settings rather than preserving them. It also assumes column A is filled for every record; if that is not true, base the last-row calculation on a reliably populated column instead.
Change the filter criteria
For exact text matching, replace Criteria1:="Open" with the value you need, such as Criteria1:="West". A number can be used as a criterion as well. Text criteria are generally not case-sensitive in Excel’s standard AutoFilter behavior. Wildcards allow partial text matches: an asterisk represents any number of characters, while a question mark represents one character. For instance, Criteria1:="*east*" matches values containing “east.”
To match either of two text values in the same field, provide a second criterion and an OR operator. For example, the Status filter can show Open or Pending:
dataRange.AutoFilter Field:=2, Criteria1:="Open", _
Operator:=xlOr, Criteria2:="Pending"
For dates and numbers, choose criteria that suit the values stored in the cells. Dates imported as text may not behave like real Excel dates, and regional date formats can make literal date criteria confusing. For date ranges or more complex conditions, test the macro on a copy and verify the visible results before using it on important data.
Choose the sort key and direction
The filter field and sort key do not have to be the same column. To filter Status for Open but sort those records by Date, keep Field:=2 in the filter call and change the sort key range to the Date cells, such as ws.Range("C2:C" & lastRow). Use Order:=xlAscending for ascending order or Order:=xlDescending for descending order. Ascending order puts text alphabetically and numbers from low to high; for dates, it runs from earliest to latest.
For a multi-level sort, add more sort fields before setting the range and applying the sort. Add the most important key first, then the next tie-breaker—for example, sort by Status and then Date. Each key range should cover the records, not the header. Keep .Header = xlYes when the selected range includes a header row; use xlNo only when it does not. Misidentifying the header can cause Excel to move a heading as though it were a record.
Filtering and sorting an Excel table
If the data is formatted as an Excel table with Insert > Table, the table already has filter dropdowns and expands as records are added. Tables can make VBA easier to maintain because the table has a name and named columns. You can refer to it through a ListObject instead of rebuilding a range address each time. This is especially helpful when the list grows or its columns move.
For a simple, fixed worksheet list, a range-based macro like the example is straightforward. For a table, use the table’s own range and sort configuration so the operation remains tied to that table. Avoid mixing a table’s data range with unrelated adjacent cells: the sort range should include every field that belongs to each record, or values can become detached from their rows.
Common problems and fixes
- The wrong column gets filtered. Recount the field position from the first column of the AutoFilter range. A range beginning in column C still uses
Field:=1for column C. - Rows separate from their related values. The sort range must span all columns in each record. If the key is in column B but the list occupies A through F, set the sort range to A:F, not just the key column.
- The header moves or is sorted. Confirm that the range starts at the header and that
.Header = xlYes. If the range begins with data only, use the appropriate no-header setting. - The macro stops on ShowAllData. That method raises an error if no filter is currently hiding rows. Check
ws.FilterModebefore calling it; usews.AutoFilterMode = Falsewhen you intend to remove filter arrows and their settings. - The final row is detected too early. The example searches column A. If that column has blanks in the middle or at the bottom, use another consistently populated column or a table’s data range.
- Filtered-out rows are not in the order expected. Decide whether the task is to sort visible results or the complete list. Clear the filter before sorting the full dataset, then apply the desired filter again.
Also check that the workbook is not protected in a way that prevents sorting or filtering, and that the worksheet name in the code matches exactly. If a macro is blocked, review Excel’s macro security settings rather than enabling unknown code indiscriminately.
When to use VBA—and alternatives
VBA is useful when the same filter-and-sort sequence must run repeatedly, when it is triggered by a button, or when it forms part of a larger workbook process. For one-time work, use the header dropdowns to filter and the Data tab’s sort commands to arrange records. These manual steps are easier to inspect and do not require enabling macros.
Microsoft 365 also includes dynamic-array functions such as FILTER and SORT for creating formula-driven result lists in supported versions. Those functions return results elsewhere and do not rearrange the original rows. Choose them when a live, separate view is preferable; choose a table filter or VBA sort when the original records need to be shown or reordered directly.
Frequently Asked Questions
How do I sort an AutoFilter result in VBA?
Apply the filter with the range’s AutoFilter method, then configure the worksheet’s Sort object with a key, full data range, header setting, and sort order. Call .Apply to perform the sort.
Does AutoFilter sort data automatically?
No. AutoFilter controls which records are visible. Sorting is a separate operation, so a macro must explicitly set up and apply a sort if ordered results are required.
Can I filter one column and sort by another?
Yes. Set the AutoFilter field to the column used for the criteria, then specify a different key range in the Sort object. Ensure the sort range still covers all columns in each record.
How do I remove an AutoFilter in VBA?
Use ws.AutoFilterMode = False to remove the filter arrows and filter setup. To show all rows while keeping the dropdowns, check ws.FilterMode and call ws.ShowAllData only when a filter is actively hiding rows.
Can Excel for the web run an AutoFilter VBA macro?
No. Excel for the web can work with worksheet filters, but it cannot run VBA macros. Open the workbook in desktop Excel to use the macro; Excel for the web supports Office Scripts instead, which use a different automation system.
Related
- Excel Text to Columns Wizard: Split Data Easily
- Excel Text to Columns: Why Leading Zeros Disappear
- Excel Text to Columns: What the Text Qualifier Does
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Out of Memory Error: Causes and FixesAn Excel VBA “Out of memory” error usually means Excel cannot allocate enough usable memory for the macro’s…
- Excel VBA: Open a Worksheet by Name or IndexTo open a worksheet in Excel VBA, activate it with a statement such as ThisWorkbook.Worksheets("Data").Activate; replace Data with…
- Excel VBA UserForm Animation Effects: What Really WorksExcel VBA UserForms have no built-in animation setting for effects such as fading, sliding, or zooming. You can…
- Keyboard Macro Recorder Free? Tools, Limits and SafetyYes—free keyboard macro recorders are available, especially for Windows. Some capture keystrokes and mouse actions for replay, while…
- VBA Copy and Paste Values Across All Excel Tabs SafelyYes. In Excel for Microsoft 365 on Windows, a VBA macro can take the selected range on the…
- VBA Split Function: How to Split Text into an ArrayIn VBA, use Split(text, delimiter) to break a string wherever a chosen delimiter appears; the result is an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.