How to Sort an Excel Table with VBA (3 Different Ways)

Sorting data helps keep your Excel tables organized, readable, and ready for analysis. While manual sorting works for small datasets, VBA allows you to automate the process, saving time and reducing errors, especially when working with large or dynamic tables.

In this article, you will learn four practical VBA methods to sort an Excel Table. We will cover how to sort by a single column value, sort by multiple columns, sort by cell color, and sort by icon, each method giving you greater control over how your table is organized.

Sub SortTableByPrice()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Set ws = ThisWorkbook.Sheets("Sheet1")              ' Target worksheet
    Set tbl = ws.ListObjects("ProductTable")            ' Reference the Excel Table
    ' Sort the Price column in ascending order
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=tbl.ListColumns("Price").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
    MsgBox "Table sorted by Price (Low to High).", vbInformation
End Sub

Sorting a table by a specific column (like Price) is one of the most common ways to organize data. Instead of doing this manually, you can use VBA to sort your Excel ListObject (table) with one line of code.

In this example, we will sort the Product Table by the Price column in ascending order using VBA. The macro targets the table directly, making it robust even if the number of rows changes.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor
➤ Go to Insert >> Module

➤ Paste the following code:

Sub SortTableByPrice()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Adjust if needed
    Set tbl = ws.ListObjects("ProductTable")  ' Use the actual name of your table
    ' Apply sort on the Price column using the header name
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=tbl.ListColumns("Price").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
    MsgBox "Table sorted by Price (Low to High).", vbInformation
End Sub

➤ Go back to Excel
➤ Press  Alt  +  F8  , select SortTableByPrice, and click Run

➤ Your table will be sorted by Price, from lowest to highest.

When analyzing datasets like product inventories, sorting by just one column isn’t always enough. For example, you might want to first sort by Category alphabetically, and then by Price within each category. This dual-level sort ensures better organization and readability.

In this method, we’ll sort the Product Table by Category (ascending) and then Price (descending).

Steps:

➤ Press  Alt  +  F11  , choose Insert >> Module, and paste this code:

Sub SortTableByCategoryAndPrice()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change sheet name if needed
    Set tbl = ws.ListObjects("ProductTable")  ' Replace with your actual table name
    ' Apply two-level sort: Category then Price
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=tbl.ListColumns("Category").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=tbl.ListColumns("Price").Range, _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Header = xlYes
        .Apply
    End With
    MsgBox "Sorted by Category (A-Z), then Price (High to Low).", vbInformation
End Sub

➤ Return to Excel, press  Alt  +  F8 , select SortTableByCategoryAndPrice, and click Run

➤ Your table will now be sorted by Category, and within each Category by Price (descending)

Sorting by cell color is useful when you’ve visually flagged rows, such as marking urgent items in red or completed ones in green. This macro sorts your Excel Table based on the fill color in the Status column.

In this method, we’ll sort the Product Table by the color in column E (Status). The macro places all rows with a specific fill color (e.g., red) at the top of the table.

Steps:

➤ Press  Alt  +  F11  , go to Insert >> Module, and paste the following code:

Sub SortTableByStatusColor()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim colorToSort As Long
    Dim sortCol As Range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change if needed
    Set tbl = ws.ListObjects("ProductTable") ' Replace with your table name
    ' Define the color to sort (e.g., red)
    colorToSort = RGB(255, 0, 0)
    ' Define the Status column (assumed named "Status")
    Set sortCol = tbl.ListColumns("Status").DataBodyRange
    ' Perform the sort by cell color in the Status column
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add( _
            Key:=sortCol, _
            SortOn:=xlSortOnCellColor, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal _
        ).SortOnValue.Color = colorToSort
        .Header = xlYes
        .Apply
    End With
    MsgBox "Rows with red fill in Status column sorted to top.", vbInformation
End Sub

➤ Return to Excel, press  Alt  +  F8  , select SortTableByStatusColor, and click Run.

➤ The table is now sorted to place all red-filled rows in the Status column at the top.

Frequently Asked Questions

Can VBA sort an Excel table automatically when data changes?

Yes! You can use the Table.Update or event-triggered macros (like Worksheet_Change) to reapply sort order whenever data updates, ensuring your table remains correctly ordered.

How do I reference a table column in VBA for sorting?

In VBA, use ListObject.ListColumns(“ColumnName”).DataBodyRange to identify the column. Then apply sorting methods like .SortFields.Add or .SortFields.AddIconSort for value or icon-based sorting.

Does sorting a table in VBA affect formulas or formatting?

No. VBA’s table sorting moves rows entirely, preserving formula references, cell formatting, and conditional rules. You’re only changing row order, not altering cell content or structure.

Can VBA sort by more than one column in a table?

Absolutely. Use .SortFields.Add multiple times on different columns, set their order, then .Apply. VBA supports multilevel sorts, such as sorting by Category then by Value.

Wrapping Up

Sorting tables with Excel VBA gives you powerful control over how your data is organized. Whether you’re sorting by values, multiple columns, colors, or icons, VBA lets you automate the process for speed and consistency. With just a few lines of code, you can handle dynamic datasets and keep everything neatly ordered. Try out the methods in this article to personalize your sorting tasks to your exact needs.