Yes. In Excel VBA, apply AutoFilter to a range that includes its headers, then sort that same complete range with the worksheet’s Sort object. The filter’s Field number is relative to the first column of the filtered range, while the sort key identifies the column used to order the records.
What AutoFilter and Sort do together
AutoFilter and sorting solve different problems. AutoFilter hides records that do not meet a condition; sorting changes the order of records in the list. For example, a sales sheet can be filtered to show orders with a status of “Open” and sorted by order date, while keeping each order’s customer, amount, and status together on its row.
Applying a filter does not itself sort the data, and sorting does not itself filter it. In VBA, you normally set the filter criteria and then apply a sort to the full list range. Keeping the whole list as the sort range is important: sorting only one column can separate values from the other fields in their records.
The method below is for desktop Excel for Microsoft 365 on Windows. VBA is available in desktop Excel for Mac too, though the VBA editor and some interface details differ. Excel for the web can display a workbook containing macros, but it cannot run VBA macros.
How the Field number works
The Field argument in Range.AutoFilter counts columns from the left edge of the range receiving the filter. It is not necessarily the worksheet’s column number. If the filtered range is A1:D100, Field 1 means column A, Field 2 means B, and Field 3 means C. If the range instead starts at B1, Field 1 means B.
That distinction is a frequent source of mistakes. Suppose column C contains Status and the filter range begins in A. Use Field:=3 to filter Status. The sort key, by contrast, is an actual worksheet range, such as B2:B100, if column B contains the dates to sort by. The filter field and sort key therefore use different ways of identifying columns.
Use one header row, followed by records with consistent columns. Include all columns that belong to each record in both the filter range and sort range. A range with a blank row or a second header in the middle may be treated as separate data, so check the layout before automating it.
A complete VBA example
This macro assumes a worksheet named Data, headers in A1:D1, records below them, Status in column C, and a date or other sort value in column B. It filters for Open in column C, then sorts the list by column B in ascending order. The last row is found by looking up column A, so that column should be populated on every record.
Save a copy of the workbook before running the macro. VBA changes cannot be undone with Ctrl+Z, so a backup is the simplest way to recover if the result is not what you intended.
Sub FilterAndSortData()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
' Use the worksheet in the workbook containing this code.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last record, assuming column A is filled for every record.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub ' No records below the header.
Set dataRange = ws.Range("A1:D" & lastRow)
' Clear any existing criteria before setting this example's filter.
If ws.FilterMode Then ws.ShowAllData
If ws.AutoFilterMode Then ws.AutoFilterMode = False
' Field 3 is column C because the range starts in column A.
dataRange.AutoFilter Field:=3, Criteria1:="Open"
' Sort the full list by column B, keeping each record together.
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
The example removes any existing worksheet AutoFilter arrows before adding its own, so it is intended for a simple range rather than a sheet where other filter setups must be preserved. It also exits when there is no record below the header. If the macro is stored in a different workbook, ThisWorkbook refers to the workbook containing the macro, not whichever workbook happens to be active.
How to adapt the filter and sort
Change the range, field number, criteria, and sort key to fit the sheet. For instance, if the list is A1:F500, Status is in E, and Priority is in B, use Field 5 to filter Status and a key range in B to sort by Priority. Keep the sort range set to all six columns; changing only the key changes the order, not the size of the records being moved.
- Filter for a different exact text value: replace
Criteria1:="Open"with the text to match, such asCriteria1:="Closed". - Sort descending: replace
Order:=xlAscendingwithOrder:=xlDescending. - Use another sort column: change the key range from column B to the desired worksheet column, keeping the first key cell below the header.
- Change the list’s width: update both the range definition and the sort range so that every field in each record is included.
Text criteria such as “Open” match the cell value, not a visual format such as fill color. Wildcards can be useful for text searches: for example, a criterion containing an asterisk can match text beginning with a phrase. For multiple conditions, AutoFilter supports additional criteria arguments, but the correct setup depends on whether the conditions should be combined with AND or OR. Test those criteria on a small copy of the data before using them in a routine macro.
Using Excel tables instead of fixed ranges
If the data is an Excel table, it already has headers and filter dropdowns, and the table expands as new records are added. That can be more reliable than hard-coding a range such as A1:D1000. You can refer to a table in VBA through a ListObject and use its range and column names, which makes the intent clearer when the table’s columns may move.
For table-based code, use the table’s own range for filtering and sorting rather than selecting worksheet cells by hand. The AutoFilter field index is still relative to the table’s leftmost column. If you identify a table column by its header name, confirm that the header text is unique and spelled exactly as it appears in the workbook. A table is especially useful when the number of records changes regularly; a simple fixed range can still be appropriate for a stable, small list.
Common problems and how to fix them
- The wrong column is filtered. Recount Field from the first column of the range passed to AutoFilter, not from worksheet column A. A range starting at C makes its third field column E.
- The macro reports an AutoFilter or sort error. Check that the range exists, the sheet name is exact, and there is a header row. Verify that the sort key lies inside the data range and does not include the header.
- Only one column appears sorted. The sort’s SetRange must cover the full list, not just the key column. Otherwise values in different columns can become mismatched.
- The filter is missing or shows unexpected results. Check for extra spaces, inconsistent spelling, blank cells, or a filter range that stops before the last record. Text that looks identical may contain leading or trailing spaces.
- The last record is left out. The sample finds the last row using column A. If column A has gaps, use a column guaranteed to be filled or use a table that expands with its records.
- Existing filters disappear. The sample deliberately clears the worksheet’s current AutoFilter setup. Remove or revise those clearing lines if the macro must preserve a user’s existing filter state.
Merged cells, blank rows inside a list, and inconsistent headers can also make sorting unpredictable. A clean rectangular dataset—a single header row and one record per row—is the most dependable structure for both filtering and sorting. Test on a copy if the workbook has unusual layouts, manually hidden rows, or multiple filter controls.
VBA versus applying the steps manually
For a one-time task, Excel’s ribbon may be simpler: select a cell in the list, choose Data > Filter to show filter dropdowns, set the desired condition, and use the sort commands on the Data tab. In a macro, explicit ranges and criteria are more repeatable, especially when the same operation must run after importing new data or as part of a larger process.
The trade-off is that VBA requires a dependable data layout and careful assumptions. A manual sort lets a person inspect the result immediately; a macro can repeat an incorrect field number quickly. A useful approach is to test the filter and sort manually on a copy, confirm which columns and values matter, and then encode those choices in VBA. On Windows, run macros from desktop Excel with macro-enabled files where appropriate; on Mac, desktop Excel supports VBA, but do not expect the web app to execute it.
Frequently Asked Questions
Does AutoFilter sort data automatically?
No. AutoFilter shows or hides rows based on criteria. Add a separate sort operation when you also want the matching records ordered.
Does Field refer to the worksheet column number?
No. Field is counted from the leftmost column of the range being filtered. If that range starts in A, Field 3 is C; if it starts in B, Field 3 is D.
Can I filter and sort an Excel table with VBA?
Yes. Tables expose their data and columns through VBA’s ListObject object. Use the table’s range for the filter and sort so that new table rows remain part of the operation.
Why does my sort separate values from their records?
The sort range probably includes only the key column instead of the full list. Set the sort range to all columns for each record, then specify just one column as the key.
Can I run the macro in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but VBA macros run in desktop Excel. Use desktop Excel for Microsoft 365 on Windows or a desktop Mac version that supports VBA.
Related
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Button: Add One to a Worksheet Step by StepTo insert a VBA button in an Excel worksheet, show the Developer tab, choose Developer > Insert >…
- VBA MsgBox Context: What It Means and How to Use ItIn VBA, the context argument in MsgBox is a numeric help-topic identifier: it tells the associated help system…
- Excel VBA Find and Replace: Methods and ExamplesTo find and replace text with Excel VBA, use the Range.Replace method and specify what to search for,…
- Do Excel Macros Work in Google Sheets?No. Excel macros written in VBA do not run in Google Sheets, and converting an Excel workbook does…
- VBA Cell to Range: How to Reference Cells in ExcelIn Excel VBA, a cell is already a one-cell Range object: use Set target = Worksheets("Sheet1").Range("A1") to store…
- Dim ws As Worksheet in VBA: Meaning and UseDim ws As Worksheet declares a variable named ws that can refer to an Excel worksheet, but it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.