VBA Sort Table: Excel Code, Options, and Common Issues

VBA & macros · Updated November 18, 2026

To sort an Excel table with VBA, get the table as a ListObject, add one or more sort fields, and apply the table’s sort. For example, a macro can sort a table named SalesTable by its Date column, oldest first. The method below keeps each record together and leaves the table’s header row in place.

How sorting an Excel table works

An Excel table is represented in VBA by a ListObject. Unlike a loose block of cells, a table has a name, named columns, a header row, and a data body. Sorting the table as a table is useful because the operation moves complete records: values in one row stay associated with the other values in that row. A sort on a table column does not mean sorting that column in isolation.

The table’s sort object holds the sort fields and options. A sort field identifies the column to sort by, whether to sort by values or another feature such as cell color, and the desired order. The sort object can hold several fields, which lets you define a primary sort and tie-breakers. Applying the sort changes the order of rows in the table; it does not create a separate sorted copy.

In the example below, the workbook contains a worksheet named Data and a table named SalesTable. The table has a column whose header is exactly Date. Worksheet names, table names, and column headers must match the workbook, including spaces and punctuation.

Sort a table by one column

Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this procedure. If necessary, save the file as an Excel Macro-Enabled Workbook (.xlsm).

Sub SortSalesByDate()
    Dim ws As Worksheet
    Dim lo As ListObject

    ' Get the worksheet and table by their workbook names.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    ' There is nothing to sort when the table has no data rows.
    If lo.DataBodyRange Is Nothing Then Exit Sub

    With lo.Sort
        .SortFields.Clear
        .SortFields.Add Key:=lo.ListColumns("Date").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

This sorts dates from earliest to latest. To sort latest to earliest, change Order:=xlAscending to Order:=xlDescending. The .SortFields.Clear line removes any earlier sort fields held by the sort object before the macro defines its own, so old criteria do not unexpectedly affect the result.

Run the procedure from the Visual Basic Editor, or return to Excel, press Alt+F8, select SortSalesByDate, and choose Run. On a Mac, the Visual Basic Editor shortcut is generally Option+F11; some keyboards require Fn as well. Excel for the web cannot create or run VBA macros, so use the desktop Excel application.

Sort by multiple columns

For a multi-level sort, add the most important field first, then add fields that should order records within ties. For example, sort sales by region alphabetically and then by revenue from largest to smallest. This is the VBA equivalent of adding levels in Excel’s Sort dialog.

Sub SortSalesByRegionAndRevenue()
    Dim ws As Worksheet
    Dim lo As ListObject

    ' Identify the table to sort.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    If lo.DataBodyRange Is Nothing Then Exit Sub

    With lo.Sort
        .SortFields.Clear
        ' First field is the primary sort: region A to Z.
        .SortFields.Add Key:=lo.ListColumns("Region").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        ' Second field breaks ties: revenue high to low.
        .SortFields.Add Key:=lo.ListColumns("Revenue").Range, _
            SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

The first field groups records by region; the second orders rows within each region by revenue. If two records have the same region and revenue, their relative order is not specified by these criteria. Add another sort field, such as a customer ID, if a consistent tie-break is important. The sort fields must refer to columns in the same table.

Choose the right sort order and key

Pick the sort order to match the data’s meaning. Text is commonly sorted A to Z or Z to A, numbers smallest to largest or largest to smallest, and dates oldest to newest or newest to oldest. Excel also supports custom lists, such as weekday or month order, when a standard alphabetical order is not appropriate. A custom-list sort needs additional configuration; simply sorting text ascending puts April before February because it sorts the words alphabetically.

  • Dates: Confirm that the cells contain real Excel dates, not text that only looks like a date. Text dates may sort alphabetically or inconsistently.
  • Numbers: Check for numbers stored as text, currency symbols typed into values, or inconsistent decimal conventions. These can produce a surprising order.
  • Blanks: Decide whether empty cells belong at the beginning or end of the result. Test the workbook’s actual data if blank placement matters to a report or workflow.
  • Case and duplicates: The example sets .MatchCase = False, so capitalization is not used as a distinction. Identical values remain tied unless further fields are added.

When a sort depends on a business rule rather than a plain ascending or descending order, consider adding a helper column that assigns each category a numeric rank. Sorting by that rank and then by the desired secondary key is often easier to maintain than relying on a complicated custom order.

Use table names and headers carefully

Table names are not necessarily the same as worksheet names. To find or change a table’s name, click inside the table and use Table Design > Table Name on the ribbon. To inspect the column headers, look at the table’s header row. VBA’s ListColumns("Date") refers to the header text, not to a worksheet column letter such as D.

Using table and column names makes a macro more resilient if someone inserts a worksheet column or moves the table. However, renaming a table or header can break code that still expects the old name. If a macro should work on the currently selected table rather than a particular one, it can be written to use the active selection, but that approach is more sensitive to where the user clicks and is less predictable for repeatable tasks.

The sample uses ThisWorkbook, which means the workbook containing the VBA code. If the macro is stored in a personal macro workbook but should sort a different open workbook, the workbook reference must be changed deliberately. Avoid relying on ActiveWorkbook or ActiveSheet without a reason: another workbook or sheet may become active before the macro runs.

Run and distribute the macro safely

Excel may block macros depending on the file’s source, security settings, and organizational policies. Do not enable macros in an unfamiliar workbook just to sort data. For a trusted workbook, use the appropriate Excel security prompt or approved organization process. A macro-enabled file must retain the .xlsm format to store VBA; saving it as a standard .xlsx file removes the macro.

Before sharing a macro, make its assumptions clear: the workbook and sheet names, table name, required column headers, sort direction, and what happens when the table is empty. Consider adding checks that report a missing worksheet, table, or column instead of allowing a less informative runtime error. If the macro is part of a recurring process, keep a clean backup and test it on a copy containing representative data, including blanks, duplicates, and unusual values.

Sorting changes the current row order, which may matter to formulas, review workflows, or downstream exports that assume a particular arrangement. Table formulas generally remain associated with their rows as Excel sorts the table, but a separate process that relies on physical row positions may not. Explain the macro’s effect to people who use the workbook, and do not treat a successful sort as a substitute for checking the resulting data.

Common problems and how to fix them

  • “Subscript out of range”: Check that the worksheet name in Worksheets("Data") is correct and that the macro is looking in the intended workbook.
  • “Application-defined or object-defined error”: Verify that the table exists, the column header is spelled exactly as expected, and the selected sort key belongs to that table.
  • Rows appear to separate: Make sure the macro sorts the ListObject, not just a single worksheet column. Sorting one column independently can detach values from their records.
  • Dates or numbers sort strangely: Inspect the underlying cell values for text-formatted dates or numbers. Apply consistent data types before sorting.
  • The macro seems to do nothing: Check whether the table has data rows, whether macros are enabled, and whether the intended procedure was run. An empty table exits at the explicit guard in the sample.

For debugging, step through the macro in the Visual Basic Editor and inspect the worksheet, table, and column references. A frequent misconception is that the table’s visible filter arrows themselves perform sorting automatically: they provide a manual interface, but VBA must still define and apply the sort criteria. Another is that sorting is reversible with Undo; macro actions generally clear Excel’s undo history, so a saved backup is the safe recovery option.

VBA compared with manual and formula-based sorting

For a one-off sort, using the table header’s filter arrow or Data > Sort is usually quicker and easier to inspect. The ribbon Sort dialog is also convenient for choosing several levels, sort-on options, or custom lists without writing code. VBA is more useful when the same sort must run repeatedly, when a button should apply a standard order, or when sorting is one step in a larger workbook task.

A formula-based sorted result is different: it can display a separate, recalculating view while leaving the source table’s row order unchanged. Dynamic-array sorting functions are available in Microsoft 365 and Excel 2021 or later, but they do not replace a macro when the requirement is to reorder the original table in place. Choose based on the desired outcome: interactive one-time work, an automatically updated separate view, or a repeatable action that changes the table’s order.

Frequently Asked Questions

Can VBA sort an Excel table without including the header row?
Yes. Sorting through the table’s ListObject.Sort object treats the header as a header, not as a data record. The example also sets .Header = xlYes to state that explicitly.

How do I sort a table from largest to smallest?
Set that field’s order to xlDescending. For numbers, this means largest to smallest; for dates, newest to oldest; and for text, Z to A.

Can I sort by two columns in VBA?
Yes. Clear the existing sort fields, add the primary key, then add the secondary key and apply the sort. Each additional field breaks ties left by the earlier fields.

Can I run a VBA sort in Excel for the web?
No. Excel for the web can open workbooks containing macros, but it cannot run or edit VBA. Run the macro in desktop Excel for Windows or Mac.

Why does my table sort dates in the wrong order?
Some apparent dates may be text rather than Excel date values. Check the source data and convert inconsistent entries to real dates before applying the sort.

Related

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.