VBA Sort Columns in Table. We can sort columns in table using the SortFields object of Add method in Excel. Sort single column or multiple columns in table either in ascending or descending order. We can also sort data in columns by icon, date, number, string, cell color, date, etc. Let us see syntax, different examples using VBA. Also find and learn step by step instructions to run VBA example macro code.
- Objective
- VBA Syntax for the SortFields object of Add method in Excel
- VBA Sort Single Column in Table in Excel
- Sort Multiple Columns in Table in Excel VBA
- VBA Sort Table by Cell Color in Excel
- VBA Sort Table Range in Ascending order in Excel
- Sort Table Range in Descending order in Excel
- Instructions to Run VBA Macro Code
- Other Useful Resources
VBA Syntax for the SortFields object of Add method
Let us see the syntax for the SortFields object of Add method in Excel. We use this syntax to sort tables in Excel sheet.
SortFields.Add (Key, SortOn, Order, CustomOrder, DataOption)
Where
Key is a required parameter. It contains a range data type. It represents a key value to sort.
All the below parameters or arguments are optional and contains variant data type.
SortOn: It represents field to sort on.
Order: It represents the sort order.
CustomOrder: It represents custom sort order.
DataOption: It represents the data option.
VBA Sort Single Column in Table in Excel
Here is a simple example macro to sort single column in table in Excel using VBA. In this example we define sheet name, table name, and column name which needs to sort. Default it sorts in ascending order. The Sort object is used to apply sort to Table. And the Clear method is used to ensure that the previous sort is cleared.
'VBA Sort Single Column in Table in Excel Sub VBAF1_Sort_Single_Column_in_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject Dim rRange As Range 'Define Variable sTableName = "ExTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Range Set rRange = Range("ExTable[Salary]") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Sort Single Column Table With loTable.Sort 'specify header is available or not .Header = xlYes 'Clear if any existing sort .SortFields.Clear 'Specify Column to sort .SortFields.Add Key:=rRange, SortOn:=xlSortOnValues 'sort specific column in a table .Apply End With End Sub
Output: Here is the example output screenshot of above macro procedure.
Sort Multiple Columns in Table in Excel VBA
Let us see another example to sort multiple columns in table in Excel VBA. Here we have to specify multiple column names to sort multiple columns data in table.
'Sort Multiple Columns in Table in Excel VBA Sub VBAF1_Sort_Multiple_Columns_in_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject Dim rRange1 As Range Dim rRange2 As Range 'Define Variable sTableName = "ExTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Range Set rRange1 = Range("ExTable[Name]") Set rRange2 = Range("ExTable[Salary]") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Sort Single Column Table With loTable.Sort 'specify header is available or not .Header = xlYes 'Clear if any existing sort .SortFields.Clear 'Specify Column to sort .SortFields.Add Key:=rRange1, SortOn:=xlSortOnValues .SortFields.Add Key:=rRange2, SortOn:=xlSortOnValues 'sort specified column in a table .Apply End With End Sub
Output: Here is the example output screenshot of above VBA Code.
VBA Sort Table by Cell Color in Excel
Let us see another example to sort table by cell color in Excel using VBA. You can define your own cell colors. And change RGB color in the below procedure . In this example we have seen 3 different outputs.
'VBA Sort Table by Cell Color in Excel Sub VBAF1_Sort_Table_ByCell_Color_in_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject Dim rRange1 As Range 'Define Variable sTableName = "ExTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Range Set rRange = Range("ExTable[Salary]") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Sort Single Column Table With loTable.Sort 'specify header is available or not .Header = xlYes 'Clear if any existing sort .SortFields.Clear 'Specify Column to sort .SortFields.Add(Key:=rRange, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(255, 255, 0) 'sort specified column in a table .Apply End With End Sub
Output: Here is the example output screenshot of above VBA Code.
Note: Find the following link for more numbers of color index in Excel VBA.
VBA Sort Table Range in Ascending order in Excel
Here is one more example to sort table range in ascending order in table in Excel VBA. In this example we are sorting name column in ascending order.
'VBA Sort Table Range in Ascending order in Excel Sub VBAF1_Sort_Table_Range_in_Ascending_Order_in_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject Dim rRange1 As Range 'Define Variable sTableName = "ExTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Range Set rRange = Range("ExTable[Name]") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Sort Single Column Table With loTable.Sort 'specify header is available or not .Header = xlYes 'Clear if any existing sort .SortFields.Clear 'Specify Name Column to sort in ascending order .SortFields.Add Key:=rRange, SortOn:=xlSortOnValues, Order:=xlAscending 'sort specific column in a table .Apply End With End Sub
Sort Table Range in Descending order in Excel
Here is one more example to sort table range in descending order in table in Excel VBA. In this example we are sorting salary column in descending order.
'VBA Sort Table Range in Descending order in Excel Sub VBAF1_Sort_Table_Range_in_Descending_Order_in_Table() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject Dim rRange1 As Range 'Define Variable sTableName = "ExTable" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Define Table Range Set rRange = Range("ExTable[Salary]") 'Define Table Object Set loTable = oSheetName.ListObjects(sTableName) 'Sort Single Column Table With loTable.Sort 'specify header is available or not .Header = xlYes 'Clear if any existing sort .SortFields.Clear 'Specify salary Column to sort in descending order .SortFields.Add Key:=rRange, SortOn:=xlSortOnValues, Order:=xlDescending 'sort specific column in a table .Apply End With End Sub
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel VBA Tables and ListObjects
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog