VBA to Sort Table in Excel (4 Methods)

Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to sort the table in Excel with VBA.

Things to Know Before Implementing VBA to Sort Table in Excel

There are some parameters that you have to use frequently while working with the Sort method of VBA. So here we will discuss some of the parameters to make you familiar while you write the code.

VBA to Sort Table in Excel: 4 Methods

This section will show you how to sort Excel tables by considering value, colours, icons and multiple columns with VBA code.

1. Embed VBA to Sort Table by Value in Excel

Considering the following example we will sort this table by the values present in the Mark column in descending order.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• 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

Your code is now ready to run.

Here,

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will see that the column in your table is now sorted in descending order.

Read More: Excel VBA to Sort in Descending Order

2. Insert VBA Macro to Sort Table for Multiple Columns

You can also sort a table for multiple columns in Excel with VBA macro.

From the above table, we will sort the columns Name and Department in ascending order.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

Here,

• Run this code and you will get both of the columns of the table sorted in ascending order.

Read More: Excel VBA to Custom Sort

3. Implement Macro to Sort Table by Cell Color in Excel

You can also sort a table according to the cell color that it contains.

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

Steps:

• As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

Here the RGB codes that we provided, you can find it or any other RGB code that you want by following the gif given below.

• Just click on the coloured cell.
• In the Home tab, click on the arrow beside Fill Color then select More Colors. You will see the RGB codes in the Custom tab of the appeared Colors pop-up box.

• Run this code and your table will be sorted based on the colours.

4. Apply VBA to Sort Excel Table by Icon

Suppose the table of the dataset has icons for better readability. You can sort the table based on the icons in Excel with VBA macro.

Look at the above dataset. Here the table has icons beside the number values in the Marks columns so that we can understand which student has good, bad or average results.

Note that, if you don’t know how you can insert an icon inside a cell, you can simply do this with the Conditional Formatting feature in Excel.

• Select the whole range or column.
• Go to Conditional Formatting -> Icon Sets. Then pick any icon sets that you want from the option.

Steps to sort a table based on icons are given below.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

Here,

• Run this code and the table will be sorted based on the icons.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to sort the table in Excel VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• Excel VBA to Sort Alphabetically
• Excel VBA to Sort a ComboBox List Alphabetically
• Excel VBA Sort Array Alphabetically
• [Fixed!!] VBA Sort Not Working in Excel
• How to Sort Array with Excel VBA
  
• Excel VBA to Sort Multidimensional Array