To sort an Excel table by one column with VBA, get the table and column by name, clear any old sort fields, add the column as the sort key, and apply the sort. The example below sorts the Excel table named Sales by its Region column in ascending order.
Sub SortTableByColumn()
Dim ws As Worksheet
Dim tbl As ListObject
Dim sortColumn As ListColumn
' Change these names to match your workbook.
Set ws = ThisWorkbook.Worksheets("Data")
Set tbl = ws.ListObjects("Sales")
Set sortColumn = tbl.ListColumns("Region")
' An empty table has no data body to sort.
If tbl.DataBodyRange Is Nothing Then Exit Sub
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortColumn.DataBodyRange, _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. This example is for Excel for Microsoft 365 on Windows; Excel for the web cannot run VBA macros.
What sorting an Excel table does
An Excel table, also called a ListObject in VBA, is a structured data range with named columns and table-specific behavior. Sorting a table by a column rearranges its data rows as complete records. For example, sorting a sales table by Region moves each row’s date, customer, and amount along with its Region value. It does not sort just the values in the selected column independently.
That distinction matters: sorting only one column in a dataset can separate values from the records they describe. Using the table’s Sort object makes the intended operation explicit and keeps the table’s rows together. The header remains a header because the sort is applied to the table and the code sets .Header = xlYes.
Table names and column names are usually more reliable than hard-coded cell addresses. If a table grows, the macro can still refer to its named column without needing to update a range such as B2:B500. Excel table names cannot contain spaces, but column headers can, so a header like Order Date can be referenced by that exact text.
Set up and run the macro
Change Data, Sales, and Region in the example to the worksheet name, table name, and column header in your workbook. To check a table’s name, click inside it and use Table Design > Table Name. The worksheet tab name and table name are different things.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if it contains macros. - Open the Visual Basic Editor with Alt+F11 on Windows. On Mac, use Option+F11; some keyboards require Fn+Option+F11.
- In the editor, choose Insert > Module, paste the code into the module, and edit the three names.
- Return to Excel and run it from Developer > Macros, or press Alt+F8 on Windows. On Mac, Option+F8 opens the macro dialog on supported keyboard configurations.
If the Developer tab is not visible, enable it in Excel’s ribbon settings. In Microsoft 365 for Windows, use File > Options > Customize Ribbon and check Developer. Excel for Mac has its own ribbon and toolbar preferences, so the exact settings dialog differs. Macro security settings can also prevent a macro from running; only enable macros in files you trust.
Choose a column and sort direction
The line Set sortColumn = tbl.ListColumns("Region") identifies the column by its header. Replace Region with the exact table heading, including spaces and punctuation. For example, if the heading is Invoice Date, use tbl.ListColumns("Invoice Date"). A misspelled or renamed header causes a VBA error because that column cannot be found.
The direction is controlled by the Order argument in .SortFields.Add. Use xlAscending for A to Z, smallest to largest, or oldest to newest; use xlDescending for the reverse. Dates sort chronologically when they are stored as actual Excel dates. If dates are text strings, their order may not match calendar order, so convert them to real dates before sorting.
For an index-based choice, use a number instead of a header name: Set sortColumn = tbl.ListColumns(3) selects the third table column. This can be useful when the header changes, but it is less self-explanatory and breaks if someone inserts or rearranges columns. Prefer the header name when the table has stable headings.
Sort by more than one column
A multi-level sort applies a primary key and then uses additional keys to order records that tie on the first key. For example, sort Region A to Z first, then sort Sales descending within each Region. Add a second .SortFields.Add before .Apply; the order in which fields are added establishes the sort priority.
' Add this after the first SortFields.Add and before .Apply.
.SortFields.Add Key:=tbl.ListColumns("Sales").DataBodyRange, _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
In this snippet, the first sort field remains the primary one and Sales breaks ties within it. Keep .SortFields.Clear at the start of the sort setup so previous sort criteria do not unexpectedly remain. If you need a third level, add another field in the same way. For more advanced requirements, such as a custom order like High, Medium, Low, use Excel’s custom-list sort options rather than assuming alphabetical order will match that sequence.
Tables versus ordinary cell ranges
The example is specifically for a formatted Excel table. It uses ws.ListObjects("Sales") to locate the table and tbl.Sort to sort its rows. If the data is a plain range rather than a table, this object lookup will fail. You can convert a contiguous data range into a table with Insert > Table, making sure the header option is correct, or write separate range-based sorting code.
Tables are often convenient for macros because they expand as rows are added and offer stable column names. A plain range may suit a fixed report or an existing layout that cannot be converted, but its sort range must be chosen carefully so every related column is included. Avoid using a sort key that covers only one column when the rest of the record should move with it.
Sorting changes the visible row order; it does not create a separate sorted result. If you need to preserve the original order, copy the source data first or add an index column containing the original row sequence. That index can later be sorted back into ascending order.
Common errors and how to fix them
- “Subscript out of range” at the worksheet or table line: Check the sheet tab name and table name. The sheet name must match exactly, and the table must exist on that worksheet.
- Error locating the column: Check the header text for spaces or spelling differences. A header with a trailing space is not the same as one without it.
- The macro runs but nothing changes: Confirm the selected column contains different sortable values, the correct workbook is active, and the data is actually inside the named table. An empty table exits the sample macro without sorting.
- Rows appear in an unexpected order: Numbers or dates stored as text can sort differently from numeric values or real dates. Check the cell values and data types, not just how they are formatted.
- Only one column moves: Verify that the data is a real table and that the macro uses its Sort object. In a range-based approach, make sure the entire record range is included.
- Macro does not appear or run: Confirm the file is saved in a macro-enabled format, macros are permitted by the organization’s security policy, and the code is in a standard module.
For a large table, sorting can take longer if the workbook recalculates formulas or runs other event macros. Do not turn off calculation or events casually: those settings affect workbook behavior and must be restored even if code encounters an error. For a straightforward table sort, the basic code is usually preferable to adding performance settings without a specific need.
VBA sorting compared with other options
For a one-time sort, use the table’s filter arrow or Data > Sort instead of writing VBA. The interface is easier to inspect and is less likely to surprise someone opening the workbook. VBA is useful when a sort should happen as part of a repeatable process, such as a report-preparation macro or a button-triggered cleanup step.
For a separate, automatically updated sorted result, a worksheet formula may be a better fit. Excel for Microsoft 365 and Excel 2021 support the dynamic-array SORT function, which returns a sorted copy without rearranging the original table. Unlike a VBA sort, a formula result recalculates as its source changes, and it may spill into neighboring cells. Excel for the web supports many worksheet functions but does not execute VBA macros.
Choose based on the outcome you want: use a manual sort for occasional work, VBA to change the table’s row order on demand, or a dynamic-array formula for a live sorted view that leaves the source order intact.
Frequently Asked Questions
How do I sort an Excel table by a column in VBA?
Reference the table with its worksheet’s ListObjects collection, add the target column’s DataBodyRange as a sort field, set the direction, and apply the table’s Sort object. The opening code provides a complete example.
Can I sort by the column number instead of its name?
Yes. Use tbl.ListColumns(3) to select the third column in that table. A header name is usually clearer, while a number can become incorrect if someone inserts or reorders columns.
Does VBA sort the header row?
Not in the example. It sets .Header = xlYes, which tells Excel that the first row of the table is a header. Keep that setting for a table with column headings.
Can I sort a table in Excel for the web with VBA?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA macros. Run the macro in the desktop Excel app, or sort manually in the web app.
Can I undo a VBA sort with Ctrl+Z?
Generally, no. Running a macro clears Excel’s normal undo history, so save a copy before sorting if you may need the original order. An index column can also make restoring the previous sequence easier.
Related
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Clear the Clipboard After Copy and PasteTo clear Excel’s copy mode after a VBA copy-and-paste operation, run Application.CutCopyMode = False after the paste. This…
- VBA Activate Workbook: Methods, Examples, and FixesTo activate an open workbook in Excel VBA, retrieve it from the Workbooks collection by its exact name…
- VBA MsgBox Carriage Return: Add Line BreaksTo put a carriage return and line break in a VBA MsgBox, join the message parts with vbCrLf:…
- How to Run Macros in Google Sheets: A Step-by-Step GuideTo run a macro in Google Sheets, open the spreadsheet in a desktop web browser, choose Extensions >…
- Excel Macro to Get Row Count: Choose the Right VBA MethodTo count data rows in an Excel macro, count a column that has one nonblank entry for every…
- Print GMT Time Using Excel VBATo print the current GMT time with Excel VBA on Windows, call the Windows GetSystemTime API and send…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.