Excel VBA Sort Order: Ascending, Descending, and Custom

In Excel VBA, set a sort field’s Order to xlAscending or xlDescending to choose its direction. For a reliable sort, define the key column, the full data range, and whether the range has headers; those settings determine what Excel sorts and whether it keeps each row’s data together.

What “sort order” means in VBA

“Sort order” can refer to two related things: the direction of a sort, or the order in which categories should appear. Direction is the familiar ascending or descending choice. For numbers, ascending runs from smallest to largest; for dates, it runs from earliest to latest. Descending reverses those sequences. Text is sorted according to Excel’s text-sorting rules and the system’s regional settings.

Category order is different. A person might want weekdays in Monday-to-Sunday order, or statuses in the order High, Medium, Low. Those are not necessarily alphabetical, so a custom list or another sort key is needed. In VBA, the direction is commonly expressed with the Excel constants xlAscending and xlDescending, rather than the words “ascending” and “descending.”

A sort field is the column or row Excel uses to compare records. The sort range is the entire block that should move together. If a table has names, departments, and salaries, sorting only the salary cells would detach salaries from the people they belong to. Set the sort range to include every column in each record, even when only one column determines the order.

A standard VBA sort using the Sort object

For Microsoft 365 desktop Excel on Windows, the Sort object makes the sort range and settings explicit. This example sorts a table in ascending order by column B. It assumes headers are in row 1 and the table occupies columns A through D. Change the worksheet name and columns to match the workbook.

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

    ' Find the final used row in the key column.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Do nothing if there are no data rows below the header.
    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

The key begins at B2 because row 1 is the header; the full range begins at A1 so the header is included in the range definition. .Header = xlYes tells Excel not to treat that first row as a record. .Orientation = xlTopToBottom sorts records vertically, which is the usual layout for a worksheet table. The equivalent descending setting is Order:=xlDescending.

The code finds the last row by looking upward from the bottom of column B. That is convenient when the data grows, but it assumes every data record has a value in column B. If the key column can contain gaps or empty cells at the bottom, find the last row from a more consistently populated column instead.

Sorting with more than one key

For ties, add further sort fields. For example, sort first by department in column B, then by surname in column A. The first field is the primary criterion; subsequent fields break ties within it. The sort range still needs to cover all columns in each record.

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

    ' Use the department column to find the data boundary.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    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
        .SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:D" & lastRow)
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Do not rely on equal-key rows remaining in their original order. If that order matters, add an explicit final key, such as a unique ID or original row number. A deliberate tie-breaker makes the output predictable and documents the intended result.

Ascending, descending, and mixed directions

Each sort field can have its own direction. A common example is sorting by department A to Z, then by sales from highest to lowest inside each department. Keep the first field ascending and set the second field to xlDescending. This is a multi-key sort, not a special “mixed” mode.

Excel interprets the direction according to the values being compared. Numeric cells sort numerically, dates sort chronologically, and text sorts by its text order. A value that looks like a number but is stored as text may not behave like a true number. For instance, text labels “1”, “10”, and “2” can sort alphabetically rather than in numeric order. Convert inconsistent data to a consistent type before sorting when the sequence looks unexpected.

Blank cells are generally placed after populated values in a standard ascending or descending sort. If blanks need to appear first, or should be grouped separately, use a helper column that assigns an explicit rank, then sort by that rank and the desired key. This is often clearer than trying to infer a special order from empty cells.

Custom orders for categories

Alphabetical order is not always useful for labels. If column C contains High, Medium, and Low, a custom order can place those categories in that sequence. The CustomOrder argument on SortFields.Add accepts a comma-separated sequence. Use it as an intentional category ranking, and ensure the values in the worksheet match the labels in the list.

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

    ' Find the last priority entry.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("C2:C" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:="High,Medium,Low", DataOption:=xlSortNormal
        .SetRange ws.Range("A1:D" & lastRow)
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

This kind of order is useful for priority, workflow status, or a manually ranked set of product groups. Check whether the category names include extra spaces or spelling variations; a value such as “Medium ” may not match “Medium” as expected. Custom lists can also be managed in Excel’s options, but specifying the sequence in the code makes the intended order visible in the macro.

Headers, ranges, and Excel tables

Header handling is a frequent cause of sort errors. With a known header row, set .Header = xlYes. Use xlNo only when the selected range contains data and no header. xlGuess asks Excel to infer the setting, which can be convenient for interactive use but is less predictable in automation. A macro should normally state the choice explicitly.

Use .SetRange to include all related columns, not just the key. A good test is to inspect a record before and after the sort: its name, ID, date, and other fields should still belong together. If the source is an Excel table, sorting the table’s data range is another option; table headers are treated separately from its data. Avoid including unrelated notes or totals beside the table unless they are meant to move with the records.

The sample macros use a worksheet named Data and fixed column boundaries. For a worksheet with a different layout, update the worksheet name, key column, and sort range together. For more complex workbooks, determine the table’s boundaries from a named range or a ListObject (Excel table) rather than guessing from a single column.

Common problems and how to prevent them

Only one column moves: The sort range probably covers only the key column. Expand it to the full table so complete rows move together.
The header is sorted into the data: Set .Header = xlYes and make sure the range starts on the header row.
The result is not truly numeric: Numbers stored as text can sort as text. Normalize the column’s data type before sorting.
The macro misses the last records: The chosen column may have blanks at the bottom. Find the last row using a column that is filled for every record, or use the table’s data range.
Rows with the same key seem to change order: Add another sort field, such as a unique ID, to specify the tie-break.
The macro reports an invalid range: Check that the worksheet exists, the last-row calculation is valid, and the key range lies inside the full sort range.

One misconception is that xlAscending means “A to Z” in every situation. It means ascending according to the value type and applicable sorting rules; dates and numbers follow their own natural sequences. Another is that sorting a filtered list necessarily sorts only visible records. Filters affect what is displayed, while the sort configuration and selected range determine the operation. Review the full range and test on a copy when hidden or filtered rows are important.

Running the macro safely and platform notes

Save a separate copy of the workbook before running a sorting macro. A VBA sort changes the worksheet’s data, and macro actions cannot be undone with Ctrl+Z. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it must retain VBA code. Run macros only from workbooks and sources you trust; Excel may block macros depending on its security settings.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, paste the procedure, and run it from the editor or the Macro dialog. On Mac, Option+F11 (sometimes Fn+Option+F11, depending on the keyboard) opens the Visual Basic Editor; the Developer tab and Macro dialog are also available in desktop Excel. Keyboard settings can vary by Mac model. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop application for this code.

Frequently Asked Questions

What is the VBA code for ascending order? In a sort field, use Order:=xlAscending. Set the key and full range as well, and specify the header setting so Excel sorts the intended records.

How do I sort descending in Excel VBA? Change that field’s order to Order:=xlDescending. For example, use it for newest dates first or largest numeric values first.

Can VBA sort by two columns? Yes. Clear the existing sort fields, add the primary key first, then add a secondary key. Each field can have its own ascending or descending direction.

How do I sort weekdays or priorities in a custom order? Add the field with a CustomOrder sequence such as Monday,Tuesday,Wednesday,Thursday,Friday, or define a helper rank column and sort by that rank.

Why does my VBA sort put 10 before 2? The values may be stored as text, so Excel is comparing characters instead of numeric values. Convert the entries to numbers, or create a consistently numeric helper column before sorting.

Can Excel for the web run a VBA sort macro? No. Excel for the web can preserve a workbook that contains VBA, but running or editing the macro requires desktop Excel for Windows or Mac.

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.