How to Sort Multiple Columns of a Table with Excel VBA (2 Methods)

In this article, we will describe how to sort multiple columns of a table using VBA in Excel, based on both a single column and multiple columns.

Sort Multiple Columns of a Table with Excel VBA (Quick View)

Sub Sort_Table_Based_on_Multiple_Columns()

Worksheets("Sheet1").Range("Table1").Sort Key1:=Range("Table1[Joining Date]"), _
                                          Order1:=xlAscending, _
                                          Header:=xlYes, _
                                          Key2:=Range("Table1[Salary]"), _
                                          Order2:=xlDescending

End Sub

Download Practice workbook

Sort Multiple Columns of a Table.xlsm

2 Suitable Ways to Sort Multiple Columns of a Table with Excel VBA

Here we have a table called Table1 in a worksheet called Sheet1 of an Excel workbook that contains three columns, namely Employee Name, Joining Date, and Salary.

We’ll sort multiple columns of this table with Excel VBA.

Method 1 – Sorting Multiple Columns of a Table based on a Single Column with Excel VBA

First, we’ll sort all the columns of this table based on a single column.

Let’s sort the whole table (Table1) of Sheet1 in ascending order of the joining dates of the employees (Seniority-wise).

Overview of the Code:

In the code, we’ll use the Sort method of VBA, which takes 3 parameters:

Key: The column based on which sorting will be made. Here it’s Table1[Joining Date].

Order: The order in which the Key column will be sorted. Here it’s xlAscending.

Header: Represents whether the table has headers or not. Here it’s xlYes (as the table has headers).

Special Note:

The Key parameter and the Order parameter can be more than one in number (in case the table is sorted based on multiple columns). So we have to number them in order like this: Key1, Order1, Key2, Order2, etc.

Here, we have only one Key and one Order, so we’ll use only Key1 and Order1.

VBA Code:

The complete VBA code will be:

Sub Sort_Table_Based_on_Single_Column()

Worksheets("Sheet1").Range("Table1").Sort Key1:=Range("Table1[Joining Date]"), _
                                          Order1:=xlAscending, _
                                          Header:=xlYes

End Sub

Output:

Run the code (after changing the inputs according to your need). The whole table will be sorted in ascending order of the joining dates of the employees.

Similar Reading

Method 2 – Sorting Multiple Columns of a Table based on Multiple Columns with Excel VBA

In the previous method, we sorted the table based on the joining dates only.

But in the joining dates there are some repetitions. That is, the joining dates of a few employees are the same. In this next example, we’ll sort these cases in the descending order of their salaries. So, we’ll sort based on the joining dates (ascending order), then based on salaries (descending order).

Overview of the Code:

We’ll again use the Sort method of VBA, but this time, there will be 2 Key parameters (Key1 := Table1[Joining Date] and Key2 := Table1[Salary]) and 2 Order parameters (Order1 := xlAscending and Order2 := xlDescending).

VBA Code:

Therefore, the VBA code will be:

Sub Sort_Table_Based_on_Multiple_Columns()

Worksheets("Sheet1").Range("Table1").Sort Key1:=Range("Table1[Joining Date]"), _
                                          Order1:=xlAscending, _
                                          Header:=xlYes, _
                                          Key2:=Range("Table1[Salary]"), _
                                          Order2:=xlDescending

End Sub

Output:

Run the code (after changing your inputs). This time the whole table is sorted in the ascending order of the joining dates of the employees first, then in the descending order of the salaries.

Things to Remember

Here we’ve used the Sort method of VBA to sort the table. There is also a built-in function in Excel called the Sort function that we can use directly to sort any range (available in Office 365 only).