Excel VBA Sort Custom Order: How to Set It Up

To sort rows in a custom sequence with Excel VBA, add a sort field with the CustomOrder argument, such as CustomOrder:="Urgent,High,Normal,Low". The example below sorts an entire data range by its Priority column while keeping each row together.

What a custom sort order does

A normal ascending sort arranges text alphabetically and numbers from smallest to largest. That is useful for names or dates, but it does not express a workflow sequence such as Urgent, High, Normal, Low. A custom order tells Excel the sequence you want for one sort key, regardless of alphabetical order.

This is different from sorting by several columns. A multi-column sort uses one key first and another key to break ties; a custom order changes the relative order of values in a single key. For example, you might put department values in the sequence Sales, Support, Operations, then sort names alphabetically within each department.

In Excel’s VBA sort model, SortFields.Add defines a sort key and its settings. Its CustomOrder argument accepts a comma-separated sequence of values for the key. You still set the full range to sort, identify whether it has a header, and apply the sort.

A working VBA example

Assume a worksheet named Tasks has headers in row 1, task data in columns A:D, and priority labels in column B. The values in B are Urgent, High, Normal, or Low. This macro sorts the data in that priority order:

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

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

    ' Do nothing if there are no data rows.
    If lastRow < 2 Then Exit Sub

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

Change the sheet name, key column, data range, and custom sequence to match your workbook. Save a copy of the workbook before running any macro: VBA changes cannot be reversed with Ctrl+Z.

How to read and adapt the macro

lastRow finds the last nonempty cell in column B, so the macro does not need a fixed row number. This approach assumes every data record has a value in the priority column. If column B can be blank while other columns still contain records, find the last row using a reliably populated column instead.

SortFields.Clear removes previously configured sort fields from the worksheet’s sort object. That helps prevent an earlier sort key from affecting a later run. Key identifies the cells whose values determine the order; it excludes the header here. Order:=xlAscending is included because Excel needs a sort direction, though the custom sequence supplies the intended order of listed labels.

SetRange is especially important: it specifies all the cells that should move together. In this example, columns A:D are sorted based on column B. Header = xlYes tells Excel that row 1 contains labels rather than a record. Orientation = xlTopToBottom sorts records down the rows, which is the usual layout for a table.

To sort by another field, change the key range, such as B2:B to C2:C, and update the custom values.
To include more record columns, expand the range in SetRange, for example from A:D to A:F.
If the selected range has no header, use .Header = xlNo and include the first data row in the key range.

Keep complete records together

Sort the full rectangular data range, not just the column containing the labels. If you sort only the Priority cells, priorities can become separated from task names, dates, or owners on the same rows. That can silently corrupt a worksheet while still producing a plausible-looking sorted column.

Before running the macro, check that the range contains the same set of records in every column and does not unintentionally include unrelated notes. If your data is an Excel Table, its rows are designed to remain together when sorted. You can use a table-specific sort instead, but the key principle remains the same: sort the records by the chosen field, not an isolated column.

The example uses a continuous range from row 1 to the last occupied priority cell. If there are totals, blank separator rows, or multiple independent blocks within that area, the macro may include them. Adjust the range logic or use the table’s actual data range so the sort covers only the intended records.

Choosing and maintaining the custom sequence

Put the most important value first in the string and continue in the exact order you want displayed. For example, a shipping-status sequence might be Not started,In progress,Waiting,Complete. This is a presentation order, not a statement about the text’s alphabetical or numeric value.

For predictable results, make the listed values match the labels used in the key column. Spelling differences, extra spaces, inconsistent labels, or values omitted from the sequence can make the result confusing. If users enter many variations, standardize the source data with a dropdown list or another validation method before sorting. A sort does not repair inconsistent values.

The comma-separated form is convenient for a short sequence. It is less suitable when a label itself contains a comma, because commas also separate the items in the custom-order string. If labels are complex, consider using short codes in the data, or sort by a separate numeric rank column instead. For example, rank Urgent as 1, High as 2, Normal as 3, and Low as 4, then sort by that rank.

Also decide how values outside the listed sequence should be handled. A custom order is clearest when every expected label is included. If unexpected or blank values are possible, test the result with representative data and decide whether to reject those values, place them in a separate group, or add an explicit rank for them.

Common problems and fixes

The order looks alphabetical. Confirm that the custom sequence is attached to the sort field through SortFields.Add, and that the macro applies the sort after setting the range and header option.
Only one column moved. Expand SetRange to include every column belonging to the records. The key range can be one column; the sort range should cover the whole record set.
The header was sorted as data. Check that row 1 really contains headers and set .Header = xlYes. If the range begins on a data row, use xlNo instead.
Some labels appear in an unexpected position. Compare the cell contents with the custom list and look for misspellings, spaces, or unlisted values. Test with a small copy of the data first.
The macro reports a subscript error. Verify that the worksheet name in Worksheets("Tasks") matches the tab name. Worksheet names in VBA must refer to a sheet that exists in the workbook.

When investigating a result, inspect the data before and after sorting on a duplicate sheet or workbook. Sorting rearranges rows; it does not change the underlying labels, but a range-selection error can move unrelated cells or separate records.

Custom order strings versus helper ranks

For a short, stable list, a CustomOrder string is direct and keeps the intended sequence visible in the macro. It is a good fit for a fixed set of statuses, priority names, or stages. If the sequence changes often, or users need to edit it without changing VBA, store each label and its rank in a small lookup table and sort using the rank field.

A helper rank also makes exceptional values easier to manage. You can assign an explicit number to every allowed label and choose a deliberate fallback for anything unknown. The trade-off is that the workbook needs an extra column or a process to calculate the rank. A custom-order string avoids that extra data, but the sequence is embedded in code.

Excel also supports custom lists through its application settings, which can be reused for repeated manual sorts. That is distinct from a sequence written directly into a VBA sort field. For a macro that should carry its ordering rule with its code, specifying CustomOrder in the sort field makes the rule visible in that procedure.

Running VBA in Microsoft 365

This example is for desktop Excel on Windows. Open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the procedure, and run it with the cursor inside the procedure by pressing F5. You can also run a saved macro from the Macros dialog. On Mac, Option+F11 opens the Visual Basic Editor on many keyboards; some keyboards require Fn as well. Excel for the web cannot run VBA macros, so open the workbook in the desktop app.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained. Excel may display a security warning when a workbook contains macros. Only enable macros in files from sources you trust; do not lower macro security globally just to run one file. If your organization manages Office security settings, follow its policy.

Frequently Asked Questions

Can I sort by a custom order without creating a custom list in Excel?
Yes. In VBA, provide the sequence in the CustomOrder argument of SortFields.Add. The example uses a comma-separated string, so you do not need to create a reusable custom list in Excel’s settings.

Can I use a custom order and then sort alphabetically within each group?
Yes. Add the custom-order field first, then add a second sort field for the within-group value, such as a name column. The first field determines the main groups; the second sorts records that share the first value.

Why does VBA sort my rows but leave the header in the data?
The header setting may not match the selected range. If the first row of SetRange contains labels, set .Header = xlYes. If it contains a real record, use xlNo.

Can the custom order contain numbers?
Yes. A custom order can represent values such as status codes or numbered categories. Make sure the source cells’ data types and labels match what the sort is expected to compare, and test with the actual workbook values.

Can I undo a VBA sort with Ctrl+Z?
No. Excel clears its normal undo history when a VBA macro changes the workbook. Save a copy before running the sort, or keep a recoverable version of the original data.

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.