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.


Download Workbook

You can download the free practice Excel workbook from here.


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.

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.

4 Methods in Implementing VBA to Sort Table in Excel

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,

  • SortTBL → Specified the table name.
  • SortTBL[Marks] -> Specified the column name of the table to sort.
  • Key1:=iColumn → Specified the column range to let the code know which column in the table to sort.
  • Order1:=xlDescending → Specified the order as xlDescending to sort the column in descending order. If you want to sort the column in ascending order then write xlAscending instead.
  • Header:= xlYes → As the column in this table has a header so we specified it with the xlYes option.

Embed VBA to Sort Table by Value in Excel

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

Result of Embed VBA to Sort Table by Value in Excel

Read More: How to Sort Data by Value in Excel (5 Easy Methods)


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,

  • 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 → Specified the column range to let the code know the first column in the table needs to be sorted.
  • Key1:=iColumn2 → Specified the column range to let the code know the second column in the table needs to be sorted.
  • Order1:=xlAscending → Specified the order as xlAscending to sort the column in descending order. If you want to sort the column in descending order then write xlDescending instead.
  • Header:= xlYes → As the columns of this table has headers so we specified it with the xlYes option.

Insert VBA Macro to Sort Table for Multiple Columns in Excel

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

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

Read More: How to Auto Sort Multiple Columns in Excel (3 Ways)


Similar Readings


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

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

With the above table as our example, we will show you how to sort it based on the colours 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.

Implement VBA Macro to Sort Table by Cell Color in Excel

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.

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

Read More: How to Sort by Color in Excel (4 Criteria)


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,

  • 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

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

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

Read More: How to Auto Sort Table in Excel (5 Methods)


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo