VBA Sort Order in Excel: Rules and Examples

In Excel VBA, sort order is set with xlAscending or xlDescending; the sort key determines which column or row Excel uses. For a table with headers, a basic sort is ws.Range("A1:C100").Sort Key1:=ws.Range("B2"), Order1:=xlAscending, Header:=xlYes. Use the Sort object when you need several keys, custom behavior, or clearer control over the full sort range.

What “sort order” means in VBA

People use “sort order” to mean a few related things. It can mean ascending versus descending, the priority of multiple sort keys, or a custom sequence such as “High, Medium, Low.” In Excel VBA, these choices are controlled separately. Getting them straight helps prevent a macro from sorting the right data in the wrong way.

Order1:=xlAscending sorts the first key from low to high, A to Z, or earliest to latest, depending on the values.
Order1:=xlDescending reverses that direction.
Key1, Key2, and Key3 identify the fields used to organize records; the first key is the primary sort.
Options such as header handling, case sensitivity, and orientation affect how Excel interprets the range.

For ordinary tables, sorting is top to bottom: Excel rearranges rows according to values in one or more columns. A horizontal sort instead rearranges columns according to values in a row. These are different from the ascending or descending direction, so a macro that sets xlAscending still needs the correct orientation.

How Excel determines the order

Excel compares the stored values in the key cells, not necessarily the way those values look on screen. Real numbers sort numerically, dates sort by their underlying date values, and text sorts alphabetically according to Excel’s language and sorting rules. A cell formatted to display a number as text is still text; formatting alone does not convert its value.

This distinction explains a familiar surprise: text entries such as “Item 1,” “Item 10,” and “Item 2” may sort alphabetically rather than in the intuitive numeric sequence. A sort does not automatically understand that part of a label is a number. If sequence matters, use a separate numeric helper column, or store the values as numbers in the first place. Numeric-looking text can sometimes be handled with the sort data option xlSortTextAsNumbers, but cleaning the source data is usually more predictable.

Excel’s sort options also affect text comparisons. In a normal sort, letter case is generally not treated as a separate ordering rule; enabling MatchCase makes case matter. Language-specific sorting rules can affect accented characters and other alphabets. If a workbook will be used in different locales, verify the result with representative data instead of assuming that every computer will order text identically.

Set ascending or descending order with Range.Sort

For a simple one-key sort, the Range.Sort method is compact. This example assumes the worksheet is named Data, row 1 contains headers, column A contains names, and column B contains amounts. It sorts the complete A:C records by amount, smallest to largest. Change the order constant to xlDescending to put the largest amount first.

Sub SortByAmount()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Sort complete records by the amount column; row 1 is a header.
    ws.Range("A1:C100").Sort _
        Key1:=ws.Range("B2"), _
        Order1:=xlAscending, _
        Header:=xlYes
End Sub

The range being sorted must include every column that belongs to each record. If you sort only column B while names and other fields remain in A and C, the amounts can become detached from their original records. Adjust the end row and columns to match the actual data. A fixed range such as A1:C100 is easy to understand, but it will not automatically include new records beyond row 100.

Before running a macro that rearranges data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a mistaken range or key can be difficult to reverse reliably. In desktop Excel for Windows, open the VBA editor with Alt+F11; on Mac, Option+F11 may require the Fn key depending on the keyboard. Excel for the web can open workbooks containing macros, but it cannot run VBA macros.

Sort by multiple keys

To sort by department and then by name, the department must be the primary key and the name the secondary key. Within each department, names are then ordered; the department groups themselves are ordered first. The Sort object makes this priority explicit and is easier to extend than a long Range.Sort argument list.

This example assumes headers in row 1 and data in A2:C100, with department in A and last name in B. It sorts the entire three-column record range, not just the two key columns.

Sub SortByDepartmentThenName()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Remove earlier sort fields so they cannot affect this run.
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A2:A100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add Key:=ws.Range("B2:B100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange ws.Range("A1:C100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Each call to .SortFields.Add adds a criterion, and the order of the fields establishes their priority. The .SetRange line is especially important: it defines the full block Excel may rearrange. If the first key is descending but the second is ascending, set those directions independently in their respective Order arguments. Clear existing fields before building a new sort so a previous sort configuration does not remain in effect.

Custom lists, case, and horizontal sorting

Alphabetical order is not always the order a business needs. For example, “Urgent, Normal, Low” is meaningful in that sequence even though alphabetical sorting would produce a different result. Excel supports custom lists for such cases. In Excel for Windows, custom lists can be managed through File > Options > Advanced > General > Edit Custom Lists, and the Sort dialog also offers custom-list sorting. VBA’s sort methods expose custom-order options, but the exact setup depends on whether the list is stored in Excel or supplied as part of a sort field. Confirm the list and the resulting order in the target workbook.

Set case behavior deliberately when it matters. With the Sort object, .MatchCase = True requests case-sensitive matching; False is commonly appropriate for ordinary names and labels. Case sensitivity is not a substitute for a clear rule about punctuation, accents, or locale: Excel’s text comparison behavior still applies.

Most data tables should be sorted vertically, with rows moving together. For a layout where each column is a record and values across a row determine column order, use horizontal orientation, such as xlSortRows in the relevant sort setting. Do not choose horizontal sorting merely because a worksheet looks wide; first identify whether records are represented by rows or columns. The wrong orientation can rearrange the wrong dimension.

Headers, blanks, and data boundaries

Tell Excel whether the range has a header. In Range.Sort, use Header:=xlYes when the first row contains labels and Header:=xlNo when it is data. The xlGuess option asks Excel to infer the situation, but inference can be wrong when the first data row resembles a heading. Explicitly setting the header option makes a macro more dependable.

Blank cells and formulas returning an empty string deserve attention. They may not behave like ordinary text values, and a visually blank formula cell is not the same as a truly empty cell. If blank placement has business meaning, test the sort with both kinds of blanks and decide whether to handle them before sorting, for example by using a helper column that marks blank records. Also check for merged cells, inconsistent data types, and gaps inside the table: these can complicate range detection and produce results that do not match the intended record structure.

A reliable range includes the header and all fields belonging to the records, while excluding unrelated notes or totals below the table. Excel tables created with Insert > Table can make data boundaries easier to manage as rows are added. With a regular range, determine the final row carefully rather than assuming that the first empty cell marks the end; a blank within a key column might otherwise make a simple last-row calculation stop too early.

Common problems and how to prevent them

Only one column moves: Expand the sort range to include every field in each record. Sort the whole table, not only the key column.
The heading is sorted as data: Set Header to xlYes or the Sort object’s .Header property to xlYes.
Numbers appear out of sequence: Check whether some values are stored as text. Convert them consistently or use an appropriate data option and test the output.
A macro sorts the wrong sheet: Qualify worksheet and range references, as the examples do with ws, rather than relying on whichever sheet happens to be active.
Old criteria unexpectedly remain: Clear the worksheet’s sort fields before adding the intended criteria.
New rows are missed: Replace a hard-coded endpoint with a correctly calculated last row or use an Excel Table and set the sort range to the table’s data.

For repeatable automation, make the macro’s assumptions visible: identify the worksheet, state where headers are, specify the complete range, and use explicit order constants. Then test on a copy with values that expose edge cases, including duplicates, blanks, text numbers, and values that differ only by capitalization. Excel does not promise that equal-key records will retain their original relative order in every sort operation, so add a secondary key if their order must be deterministic.

When VBA is useful—and when it is not

VBA sorting is useful when a task must run from a button, be repeated across many sheets, or form part of a larger macro that imports and prepares data. A manually run sort may be simpler for a one-time task: select the table and use Home > Sort & Filter > Custom Sort in Excel for Windows, then specify the sort column, direction, and whether the data has headers. Excel for Mac has equivalent sorting controls, although ribbon and dialog presentation can differ slightly.

For a live, formula-driven sorted view rather than rearranging source records, Excel for Microsoft 365 on Windows also has the SORT worksheet function. It returns a sorted array and leaves the original cells in place; VBA sorting changes the order of cells in the selected range. Choose based on the goal: use a macro when you need to modify the table or automate a workflow, and a formula when you want a separate result that updates as its source changes. VBA requires a macro-enabled workflow and cannot be run in Excel for the web.

Frequently Asked Questions

What is the VBA constant for ascending order?
Use xlAscending in the sort order argument or property. Use xlDescending for the reverse direction.

How do I sort a range by two columns in VBA?
Add two sort fields, with the primary key first and the secondary key second, set the complete record range, and apply the sort. The multi-key example above sorts by department and then name.

Does VBA sort numbers stored as text numerically?
Not necessarily. Text values are compared as text unless they are converted or an applicable option such as xlSortTextAsNumbers is used. Consistent underlying data is the safest approach.

How do I keep the header row at the top?
Set Header:=xlYes with Range.Sort, or set .Header = xlYes on the Sort object. Make sure the header row is included in the range being sorted.

Can I undo a VBA sort with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so a sort performed by a macro cannot be reversed with Ctrl+Z. Save a separate copy before running it when the original order matters.

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.