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

Things to Know Before Implementing VBA to Sort 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.

Parameter Required/ Optional Data Type Description
Key Optional Variant Specifies the range or the column whose values are to be sorted.
Order Optional XlSortOrder Specifies the order in which the sorting will be performed.

  • xlAscending = To sort in ascending order.
  • xlDescending = To sort in descending order.
Header Optional XlYesNoGuess Specifies whether the first row contains headers or not.

  • xlNo = When the column doesn’t have any headers; Default value.
  • xlYes = When the columns have headers.
  • xlGuess = To let Excel determine the headers.

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:

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

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

  • Write the 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.

Embed VBA to Sort Table by Value in Excel

  • Run the Macro:
    • Press F5 on your keyboard or select Run -> 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.

Result of Embed VBA to Sort Table by Value in Excel

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:

  • TableValue → Specified the table name.
  • TableValue[Name] -> Specified the first column name of the table to sort.
  • TableValue[Department] -> Specified the second column name of the table to sort.
  • Key1:=iColumn1 → Indicates that the first column in the table needs to be sorted.
  • Key1:=iColumn2 → Indicates that the second column in the table needs to be sorted.
  • Order1:=xlAscending → Specified the ascending order. If you want to sort in descending order, use xlDescending.
  • Header:= xlYes → Specifies that the table has headers.

Insert VBA Macro to Sort Table for Multiple Columns in Excel

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

Result of Insert VBA Macro to Sort Table for Multiple Columns in Excel

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

Implement VBA Macro to Sort Table by Cell Color in Excel

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.

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

Result of Implement VBA Macro to Sort Table by Cell Color in Excel


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 ➡️ 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 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:

  • IconTable -> Specifies the table name.
  • IconTable[Marks] -> Refers to the column containing the cell icons.
  • xl5Arrows -> We picked the set of 5 arrows from the option in the Conditional Formatting.
  • Item (1) -> Specified the first type of arrow icon.
  • Item (2) -> Specified the second type of arrow icon.
  • Item (3) -> Specified the third type of arrow icon.
  • Item (4) -> Specified the fourth type of arrow icon.
  • Item (5) -> Specified the fifth type of arrow icon.

Apply VBA to Sort Excel Table by Icon in Excel

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

Result of Apply VBA to Sort Excel Table by Icon in Excel


Download Workbook

You can download the practice workbook from here:


 

Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

2 Comments
  1. Brilliant! Thank you!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo