VBA Sort Not Working? Fix Excel Sorting Macros with Clear Ranges

If VBA sorting is not working, check that the macro is running, the sort range includes every column in each record, the key is inside that range, and the header setting matches your data. Clear old sort fields and set the range, key, and order explicitly before calling .Apply; the example below shows a dependable pattern for a simple worksheet list.

Start with the range and sort key

A sort moves records, not just the values in one column. If a table has names in column A, dates in B, and amounts in C, sorting only column B can separate dates from the names and amounts they belong to. Set the sort range to the whole list, then use a key range from the column whose values determine the order.

For example, if row 1 contains headings and records occupy A2:C50, the sort range is A1:C50 and a date key is B2:B50. The heading belongs in the overall range so it remains attached to the list, but it should not be part of the key values. The setting .Header = xlYes tells Excel that the first row of the sort range is a heading.

Also confirm that the key range and sort range refer to the same worksheet and corresponding rows. A key on another sheet, a key that starts one row too early, or a range ending before the last record can cause errors or an apparently incomplete sort.

A reliable VBA sort example

This example sorts a contiguous list on a worksheet named Data. It assumes headers are in A1:D1, records begin on row 2, column A is populated for every record, and column B is the ascending sort key. Change the worksheet, columns, and last-row logic to match the actual layout.

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

    ' Use the worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last record using a column that is filled on every record.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    ' Include all four columns so each complete record moves together.
    Set sortRange = ws.Range("A1:D" & lastRow)

    With ws.Sort
        ' Remove sort keys left behind by an earlier sort.
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Save a copy of the workbook before running any macro: VBA changes cannot be reversed with Ctrl+Z. In this example, ThisWorkbook means the workbook containing the code. If the macro is stored in Personal.xlsb or another workbook, that may not be the workbook holding the data; explicitly set the correct workbook and worksheet rather than relying on whichever sheet happens to be active.

How the Excel Sort object works

The Worksheet.Sort object stores the sort configuration. .SortFields.Clear removes existing keys; .SortFields.Add adds a key and its order; .SetRange defines all cells to be rearranged; and .Apply executes the operation. Setting these properties in the macro makes its behavior less dependent on a sort someone previously applied through the ribbon.

The key argument is a range of values used to compare records. xlAscending puts values in ascending order and xlDescending reverses that order. xlSortOnValues sorts by cell values rather than, for example, cell color. xlSortNormal uses normal value sorting. For text, what appears to be an unexpected order can come from spaces, inconsistent spelling, or numbers stored as text rather than from a failed sort.

The orientation matters too. xlTopToBottom sorts rows by a column, which is the usual arrangement for a list. xlLeftToRight sorts columns by a row and is appropriate only when the data is deliberately organized horizontally. If the macro finishes but the visible arrangement looks unchanged, verify that it is sorting in the intended direction and that the chosen key actually varies between records.

Check headers, boundaries, and last-row logic

A common cause of a bad result is a mismatch between the range and the header flag. If the first row of the selected range contains labels, use xlYes. If it contains real records, use xlNo. Avoid xlGuess in a repeatable macro: Excel has to infer whether a heading exists, and a row of text values may be mistaken for one.

Make sure the range boundary includes all records and all related columns. The example finds the last used row in column A. That is reliable only if column A is filled in every record. If column A has gaps or records continue below its last value, the macro may omit data. Choose a consistently populated column for the last-row check, or calculate the boundary using a more appropriate method for the sheet.

For a list that starts on a row other than 1, update both the range and header setting. For example, a range beginning at A5 with headings in row 5 should use a key beginning at the first data row and set .Header = xlYes. A key range that includes the heading can make the first record behave unexpectedly. Avoid sorting a partial block when adjacent columns are part of the same records.

Sort by more than one column

For a secondary sort, add another sort field after the primary field and before .SetRange. Excel applies the first field as the main ordering and subsequent fields to break ties. For example, sort by department ascending, then by employee name ascending. Both key ranges must line up with the data rows in the full sort range.

' Add these fields inside the existing With ws.Sort block.
.SortFields.Clear
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal

Here, the full range must include both key columns and every other column in each employee record. Clear the fields once, then add all desired keys in priority order. Clearing between adding the first and second key would remove the first key, so put .SortFields.Clear before the sequence, not between its entries.

Common reasons a macro appears to do nothing

The macro did not run. Check that macros are enabled for a trusted workbook and that the intended procedure was called. Excel for the web can open a workbook containing VBA, but it cannot run VBA macros; use desktop Excel.
The code points to another sheet or workbook. A misspelled sheet name can cause an error, while a valid but unintended reference can sort the wrong place. Use the sheet tab name exactly and verify whether the code should use ThisWorkbook or a different workbook reference.
The old sort configuration remains. Without .SortFields.Clear, a previous key can remain in the sort definition. Clear and rebuild the fields on each run.
The heading is included as data or data is treated as a heading. Make .Header match the first row of the range; do not rely on an automatic guess.
The selected area is incomplete. A key outside the set range, missing columns, or an incorrect last row can leave records behind or produce an error.
The values are not comparable as expected. Numbers stored as text, leading or trailing spaces, blanks, and mixed data types can affect apparent ordering. Check the underlying cell values, not just their display format.
The sheet cannot be changed. A protected worksheet may prevent sorting, depending on its protection settings. Check Review > Unprotect Sheet or the settings used when the sheet was protected; do not remove protection unless you are authorized to do so.

When VBA stops with an error, note the highlighted line and the error text instead of suppressing it with broad On Error Resume Next. In the Visual Basic Editor, use Debug > Compile VBAProject to catch compile-time issues, then step through the procedure with F8 to see which reference or operation fails. On Windows, open the editor with Alt+F11. On Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor in Excel; keyboard shortcuts can vary by keyboard and macOS settings.

Sorting an Excel table instead of a plain range

If the data is an Excel Table created with Insert > Table, table-aware sorting can be easier to maintain because the table expands as records are added. The worksheet-level example above is designed for a fixed rectangular range; if the table grows, its hard-coded ending column or row may no longer cover all the data. Confirm that the intended table columns move together and that new records are included.

Tables can be sorted interactively from a header filter arrow, and a worksheet sort can also be applied to the table’s full range. For code that specifically targets a table, use its ListObject and table sort settings rather than assuming a fixed address. The table’s name is visible on the Table Design tab when a cell in the table is selected. This is particularly useful when rows are routinely appended, but it does not remove the need to specify the correct sort column and order.

VBA sorting versus the SORT worksheet function

A VBA sort changes the order of cells in the source range. By contrast, Excel’s SORT worksheet function returns a sorted array in another location and leaves the source data in place. In Microsoft 365 and Excel 2021 or later, a formula such as =SORT(A2:D50,2,1) returns the rows sorted by the second column in ascending order, assuming the data is in A2:D50 and the result area is clear. It is not VBA and it does not rearrange the original rows.

Use VBA when the original records need to be reordered as part of a repeatable task, such as a report-building macro. Use the worksheet function when a live sorted view is more useful and you want the source list unchanged. The formula’s availability depends on Excel version, and spilled results need room in the destination cells. Neither approach fixes inconsistent source data or a wrong sort key.

Frequently Asked Questions

Why does my VBA sort run without an error but leave the data unchanged?
Check that the macro references the sheet and workbook containing the visible data, that the chosen key contains differing values, and that the sort orientation is top-to-bottom. Also verify the range boundaries and whether a filter or another view is affecting what you can see.

Should the sort key include the header row?
No. Include the header in the overall sort range when the list has headers, set .Header = xlYes, and start the key at the first data row. If the range has no header, use xlNo and include all records in the key.

Why does the macro sort only one column?
The overall range may contain only that column. Set the range to include every field belonging to each record, while keeping the sort key limited to the column that determines order. Otherwise, values in neighboring columns can become detached from their records.

Can I run a VBA sort in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro. A worksheet formula such as SORT may be an alternative if you want a sorted output rather than changing the source rows.

Can I undo a VBA sort with Ctrl+Z?
No. Running a macro clears Excel’s normal undo history, so Ctrl+Z does not reverse its changes. Save a separate copy before running a sort macro, especially when the range or key has not yet been verified.

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.