How to Sort a Table in Excel using VBA (4 Methods)

Things to Know Before Implementing VBA to Sort a Table in Excel

Before implementing VBA to sort a table in Excel, there are some parameters you need to be familiar with when working with the Sort method. Let’s discuss these parameters to help you write your code effectively.

Method 1 – Embed VBA to Sort Table by Value in Excel

Consider the following example where we want to sort a table by the values in the Marks column in descending order:

• Press Alt + F11 on your keyboard or go to the Developer tab and click Visual Basic.
• This will open the Visual Basic Editor.

• In the pop-up code window, click Insert from the menu bar and select Module.
• This creates a new module where you can enter your VBA code.

• Copy the following code and paste it into the code window:

Sub SortTableValue()
Dim iSheet As Worksheet
Dim iTable As ListObject
Dim iColumn As Range
Set iSheet = ActiveSheet
Set iTable = iSheet.ListObjects("SortTBL")
Set iColumn = Range("SortTBL[Marks]")
With iTable.Sort
  .SortFields.Clear
   .SortFields.Add Key:=iColumn, SortOn:=xlSortOnValues, Order:=xlDescending
   .Header = xlYes
   .Apply
End With
End Sub

• Let’s break down what this code does:
  • SortTBL: Specifies the table name.
  • SortTBL[Marks]: Specifies the column name of the table to sort.
  • Key1:=iColumn: Specifies the column range to let the code know which column in the table to sort.
  • Order1:=xlDescending: Specifies the order as xlDescending to sort the column in descending order. If you want to sort the column in ascending order, use xlAscending instead.
  • Header:= xlYes: Since the column in this table has a header, we specify it with the xlYes option.

• Press F5 on your keyboard or select Run and click on Run Sub/UserForm from the menu bar.
• Alternatively, click the small Play icon in the sub-menu bar.

• The column in your table will now be sorted in descending order.

Read More: Excel VBA to Sort in Descending Order

Method 2 – Insert VBA Macro to Sort Table for Multiple Columns

You can also sort a table for multiple columns in Excel using a VBA macro. Let’s say we want to sort the columns Name and Department in ascending order from the table provided.

Here are the steps:

• Open the Visual Basic Editor from the Developer tab.
• Insert a new Module in the code window.
• Copy and paste the following VBA code:

Sub SortTable()
Dim iSheet As Worksheet
Dim iTable As ListObject
Dim iColumn As Range
Set iSheet = ActiveSheet
Set iTable = iSheet.ListObjects("TableValue")
Set iColumn1 = Range("TableValue[Name]")
Set iColumn2 = Range("TableValue[Department]")
With iTable.Sort
  .SortFields.Clear
        .SortFields.Add Key:=iColumn1, Order:=xlAscending
        .SortFields.Add Key:=iColumn2, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
End Sub

Explanation:

• You can run this code, and both columns of the table will be sorted in ascending order.

Read More: Excel VBA to Custom Sort

Method 3 – Implement a Macro to Sort a Table by Cell Color in Excel

You can also sort a table based on the cell color it contains. Let’s use the example table provided above and demonstrate how to sort it based on the colors within the table.

With the above table as our example, we will show you how to sort it based on the colors that this table holds.

Follow these steps:

• As previously shown, open the Visual Basic Editor from the Developer tab.
• Insert a new Module in the code window.
• Copy and paste the following VBA code:

Sub SortTableColor()
Dim iSheet As Worksheet
Dim iTable As ListObject
Dim iColumn As Range
Set iSheet = ActiveSheet
Set iTable = iSheet.ListObjects("SortTable")
Set iColumn = Range("SortTable[Marks]")
With iTable.Sort
  .SortFields.Clear
  .SortFields.Add(Key:=iColumn, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(248, 203, 173)
  .SortFields.Add(Key:=iColumn, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(255, 217, 102)
  .SortFields.Add(Key:=iColumn, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(198, 224, 180)
  .SortFields.Add(Key:=iColumn, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(180, 198, 231)
  .Header = xlYes
  .Apply
End With
End Sub

Explanation:

• SortTable – Specifies the table name.
• SortTable[Marks] – Specifies the column containing the cell colors.
• We’ve defined specific RGB color codes for sorting purposes.
• To find RGB codes, follow these steps:
  • Click on a colored cell.
  • In the Home tab, click the arrow next to Fill Color and select More Colors.
  • You’ll find the RGB codes in the Custom tab of the Colors pop-up box.

• You can run this code, and your table will be sorted based on the colors.

Method 4 – Use VBA to Sort an Excel Table by Icon

Suppose your dataset table includes icons for better readability. You can sort the table based on these icons in Excel using a VBA macro.

In the example dataset provided, the table has icons next to the numerical values in the Marks column. These icons help us understand whether a student’s performance is good, bad, or average.

If you’re unsure how to insert an icon inside a cell, you can achieve this using Excel’s Conditional Formatting feature:

• Select the entire range or column.
• Go to Conditional Formatting and select the Icon Sets. Choose any icon set that suits your preference.

Now, let’s proceed with the steps to sort a table based on icons:

• Open the Visual Basic Editor from the Developer tab.
• Insert a new bin in the code window.
• Copy and paste the following VBA code:

Sub SortTableIcon()
Dim iSheet As Worksheet
Dim iTable As ListObject
Dim iColumn As Range
Set iSheet = ActiveSheet
Set iTable = iSheet.ListObjects("IconTable")
Set iColumn = Range("IconTable[Marks]")
With iTable.Sort
  .SortFields.Clear
  .SortFields.Add(Key:=iColumn, Order:=xlDescending, SortOn:=xlSortOnIcon).SetIcon Icon:=ActiveWorkbook.IconSets(xl5Arrows).Item(1)
  .SortFields.Add(Key:=iColumn, Order:=xlDescending, SortOn:=xlSortOnIcon).SetIcon Icon:=ActiveWorkbook.IconSets(xl5Arrows).Item(2)
  .SortFields.Add(Key:=iColumn, Order:=xlDescending, SortOn:=xlSortOnIcon).SetIcon Icon:=ActiveWorkbook.IconSets(xl5Arrows).Item(3)
  .SortFields.Add(Key:=iColumn, Order:=xlDescending, SortOn:=xlSortOnIcon).SetIcon Icon:=ActiveWorkbook.IconSets(xl5Arrows).Item(4)
  .SortFields.Add(Key:=iColumn, Order:=xlDescending, SortOn:=xlSortOnIcon).SetIcon Icon:=ActiveWorkbook.IconSets(xl5Arrows).Item(5)
  .Header = xlYes
  .Apply
End With
End Sub

Explanation:

• You can run this code, and your table will be sorted based on the icons.

Download Workbook

You can download the practice workbook from here:

Related Articles

• Excel VBA to Sort Alphabetically
• Excel VBA to Sort a ComboBox List Alphabetically
• Excel VBA Sort Array Alphabetically
• How to Sort Array with Excel VBA
  
• Excel VBA to Sort Multidimensional Array