VBA Sort Custom Order: Code Examples and Common Fixes in Excel

To sort Excel rows in a chosen sequence with VBA, add a custom sort field and give it the sequence you want, such as "Critical,High,Normal,Low". The example below sorts a table by its Priority column in that order, rather than alphabetically.

What a custom-order sort does

A normal ascending sort puts text in alphabetical order and numbers in numerical order. A custom-order sort replaces that default ranking with a sequence you define. It is useful when the order has meaning but is not alphabetical: project statuses, weekdays, shipping levels, approval stages, or a manually ranked set of categories.

For example, an alphabetical sort of High, Low, Normal, and Critical does not express urgency. A custom list can instead rank Critical first, followed by High, Normal, and Low. Excel then moves each complete row according to the value in the selected sort column.

VBA code for a custom sequence

This macro assumes a worksheet named Tasks, headers in row 1, task data in columns A through D, and priorities in column B. Change the sheet name, range, and sequence to match your workbook. In Excel for Microsoft 365 on Windows, press Alt+F11, choose Insert > Module in the Visual Basic Editor, and paste the code into the module.

Option Explicit

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

    Set ws = ThisWorkbook.Worksheets("Tasks")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Keep the header and do nothing if there are no data rows.
    If lastRow < 2 Then Exit Sub

    Set sortRange = ws.Range("A1:D"amp; lastRow)

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

In the code shown as HTML, the two range-concatenation operators should be the VBA ampersand character: use "A1:D" & lastRow and "B2:B" & lastRow in the editor. The ampersand is displayed as an HTML entity above so it remains readable in the page source. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro. Make a copy of the workbook before running it: macro changes cannot be undone with Ctrl+Z.

How the sort settings work

The SortFields.Add line defines the sort key and its custom sequence. Key identifies the priority cells, while CustomOrder gives Excel the ranking from first to last. Order:=xlAscending is used with this sequence; it does not mean Excel will alphabetize the listed values instead of following the custom order.

.SortFields.Clear removes sort fields left over from an earlier sort setup, preventing an old key from affecting this run.
.SetRange sortRange tells Excel which whole block of rows to rearrange. Include every column that belongs to each record.
.Header = xlYes tells Excel that the first row contains column names, not data to be sorted.
.Orientation = xlTopToBottom sorts records down the rows. It is the usual choice for a worksheet list.

The macro finds the last used row by looking upward from the bottom of column B. This is convenient when every task has a priority, but it will miss a row whose priority cell is blank even if other columns in that row contain data. If another column is always filled, use that column in the Cells(Rows.Count, ...) expression instead. The range passed to .SetRange must include all columns you want to stay together; sorting only column B would separate priorities from their task details.

Changing the order and adding tie-breakers

To put the most urgent category first, write the labels in the desired ranking order in CustomOrder. For example, "Urgent,In progress,Waiting,Complete" gives that exact sequence. The labels must match the worksheet values. If the cells contain In Progress but the code says In progress, Excel’s case-insensitive setting generally makes capitalization harmless; different spelling, extra spaces, or punctuation can still prevent a match.

To sort records with the same priority by another field, add a second sort field after the priority field and before .SetRange. For example, add a key for a due-date column with Order:=xlAscending to put earlier dates first within each priority group. Sort fields are applied in their listed order: the custom priority ranking is primary, and the due date breaks ties. Make sure the second key is within the range being sorted.

Values not named in the custom sequence are a common source of surprises. Do not assume they will be assigned a meaningful business rank. Check the result with representative unexpected entries, such as a blank, On hold, or a misspelled label. If every value must have an explicit position, validate the column first or use a rank table and sort by its numeric rank.

Using a custom list registered with Excel

Another approach is to register an application-level list with Application.AddCustomList, then use its list index with the OrderCustom argument of the older Range.Sort method. This is useful when you want Excel’s custom list to be available beyond one sort, or when a comma-separated CustomOrder string is inconvenient. Excel’s built-in lists, such as days of the week, are also custom lists.

Registered lists affect Excel’s custom-list collection, so code that adds one should account for cleanup and possible errors. A workbook-specific helper column is often easier to maintain: map each category to 1, 2, 3, and so on, then sort by that rank. The helper method makes the order visible to workbook users and avoids relying on a list that may have been added elsewhere in the Excel session.

Choosing the right method

Use SortFields.Add with CustomOrder for a short, fixed sequence used by one macro. It is direct and keeps the ranking beside the sort definition.
Use an Excel custom list if the same sequence is reused across Excel sorting tasks and you are prepared to manage the registered list.
Use a helper rank column for long or frequently edited sequences, values containing commas, or orders maintained by non-programmers. A small lookup table can make the ranking easy to audit.
Use a separate key column when the displayed text needs to remain unchanged but the order must be unambiguous. Sort on the key and keep the descriptive label for readers.

For a one-off manual sort, the Excel ribbon may be quicker than VBA: select the data, then use Data > Sort and choose a custom list in the order settings. VBA is more valuable when the same operation must be repeated consistently, triggered from a button, or included in a larger procedure.

Common problems and practical checks

If the sort appears alphabetical, verify that the custom field was added to the sort and that the key points to the intended column. If rows become mismatched, the sort range is probably too narrow. Include the header and all related fields in .SetRange, and set .Header correctly. A wrong header setting can either sort the column name as data or leave the first data row fixed.

Also check for inconsistent category text. Cells may look identical while containing leading or trailing spaces, nonbreaking spaces copied from a web page, or different punctuation. Clean or standardize the source values before sorting rather than adding many near-duplicate labels to the custom order. For a quick check, filter the category column and inspect its distinct visible entries.

Test the macro on a copy with at least one row for every category, two rows sharing a category, a blank, and an unlisted value. Confirm that the correct rows move together and that any tie-breaker behaves as intended. If the list is an Excel Table, use its complete table range or table sort interface rather than selecting only one table column; preserving the relationship between each row’s fields is essential.

Excel for Mac and macro requirements

The sorting object model used by this macro is available in desktop Excel for Windows and Mac, although the Visual Basic Editor menus and keyboard shortcuts differ. On Windows, Alt+F11 opens the editor and Alt+F8 opens the Macro dialog. On Mac, Option+F11 and Option+F8 are common equivalents; some keyboards require holding Fn as well. You can also open the editor from the Developer tab when it is enabled. Excel for the web can open a macro-enabled workbook but cannot run or edit VBA macros, so use desktop Excel for this procedure.

Macros may be blocked by Excel’s security settings or by organizational policy. Only enable macros in workbooks you trust. Saving as .xlsx removes VBA code, so use .xlsm to retain it, and keep a clean backup before running a macro that rearranges records.

Frequently Asked Questions

Can VBA sort a column in a custom order?

Yes. Add a sort field for that column and specify the ranking with CustomOrder, as in the example. Set the sort range to the full records, not just the category column, so each row stays intact.

Does custom order sort values alphabetically?

No. Excel follows the sequence provided in the custom order for matching values. Values that are not in the sequence may not receive the ranking you expect, so test them or assign every allowed category an explicit rank.

Can I sort by custom order and then by date?

Yes. Add the custom-order field first, followed by a date sort field. Excel uses the date as a tie-breaker among rows with the same primary category.

Why does my VBA custom sort ignore some labels?

The cell text may differ from the sequence because of spelling, extra spaces, punctuation, or an unlisted category. Compare the actual cell contents with the custom-order labels and clean or map inconsistent values before sorting.

Can I run this VBA macro 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 edit and run this code.

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.