VBA Sort Columns in Table in Excel
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.
- Overview to Sort Columns in Table in Excel
- 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 Execute Macro or Procedures
- Other Related References
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
Step by Step Instructions to run VBA macro codes
Here are the instructions to use above macro in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- See output screenshots after each macro procedure.
Other Related References
You can also learn complete details like syntax, example and etc by clicking on the following buttons.