Sort Multiple Columns with Excel VBA (Quick View)
ActiveSheet.Range("B4:D13").Sort Key1:=Range("D8"), Order1:=xlAscending
Sort Multiple Columns with Excel VBA: 3 Suitable Ways
We’ve got a data set with the names, salaries, and joining dates of some employees of a company.
Method 1 – Sort Multiple Columns of a Range Based on a Single Column with VBA in Excel
First, we’ll learn to sort multiple columns of a data set based on a single column with the Sort method of VBA.
Let’s try to sort the data set in ascending order according to the joining dates of the employees.
The syntax of the VBA Sort method is:
=Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)Don’t worry. You needn’t know all the arguments mentioned here at this moment. Knowing only the two arguments Key and Order will do.
The Key argument takes a cell reference of the column based on which you want to sort your data set.
The Order argument denotes whether you want to sort in ascending order or descending order.
Let’s understand it through an example. Here we want to sort based on a single column, so we’ll take only the Key1 and the Order1 argument.
Now, we want to sort the data set according to the joining dates (D4:D13).
So the Key1 argument needs to be any cell reference within the range D4:D13. Let it be D8.
And we want to sort in ascending order. So the Order1 argument will be xlAscending.
Therefore, the line of code will be:
ActiveSheet.Range("B4:D13").Sort Key1:=Range("D8"), Order1:=xlAscending
⧭ Output:
Run this code. You’ll find the data set B4:D13 in your worksheet sorted in the ascending order of the joining dates of the employees.
Read More: How to Sort Range Using VBA in Excel
Method 2 – Sort Multiple Columns of a Range based on Multiple Columns with VBA in Excel
We’ll sort multiple columns of a range based on multiple columns.
Let’s sort the employees first based on the descending order of their salaries, then on the ascending order of their joining dates.
We’ll again use the Sort method of VBA, but this time we’ll use the Key argument and the Order argument twice.
The Key1 argument will take a cell reference of the column Salary (Let it be C8), and the Key2 argument will take a cell reference of the column Joining Date (Let it be D8).
And Order1 will take xlDescending and Order2 will take xlAscending.
So the line of code will be:
ActiveSheet.Range("B4:D13").Sort Key1:=Range("C8"), Order1:=xlDescending, Key2:=Range("D8"), Order2:=xlAscending
⧭ Output:
Run this code. You’ll find the data set B4:D13 in your worksheet sorted first in the descending order of the salaries, then in the ascending order of the joining dates.
Read More: Excel VBA Sort Range with Multiple Keys
Method 3 – Sort Multiple Columns of a Range in a Different Location through Iteration with VBA in Excel
We can create a sorted copy of the data set in a different location by iterating through each of the rows.
Let’s sort the employees based on the joining dates.
We’ll convert the joining dates (D4:D13) to a one-dimensional Array.
Dim MyArray() As Variant
MyArray = Application.Transpose(ActiveSheet.Range("D4:D13"))
We’ll then iterate through for-loop to sort the array in ascending order.
For i = LBound(MyArray) To UBound(MyArray)
For j = i + 1 To UBound(MyArray)
If MyArray(i) > MyArray(j) Then
Store = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Store
End If
Next j
Next i
We sort the data set in the ascending order of the joining dates, in a different location.
For i = 1 To UBound(MyArray)
For j = 1 To ActiveSheet.Range("B4:D13").Rows.Count
If MyArray(i) = ActiveSheet.Range("B4:D13").Cells(j, 3) Then
ActiveSheet.Range("F4").Cells(i, 1) = ActiveSheet.Range("B4:D13").Cells(j, 1)
ActiveSheet.Range("F4").Cells(i, 2) = ActiveSheet.Range("B4:D13").Cells(j, 2)
ActiveSheet.Range("F4").Cells(i, 3) = ActiveSheet.Range("B4:D13").Cells(j, 3)
End If
Next j
Next i
The complete VBA code will be:
⧭ VBA Code:
Sub Sort_through_Iteration()
Dim MyArray() As Variant
MyArray = Application.Transpose(ActiveSheet.Range("D4:D13"))
For i = LBound(MyArray) To UBound(MyArray)
For j = i + 1 To UBound(MyArray)
If MyArray(i) > MyArray(j) Then
Store = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Store
End If
Next j
Next i
For i = 1 To UBound(MyArray)
For j = 1 To ActiveSheet.Range("B4:D13").Rows.Count
If MyArray(i) = ActiveSheet.Range("B4:D13").Cells(j, 3) Then
ActiveSheet.Range("F4").Cells(i, 1) = ActiveSheet.Range("B4:D13").Cells(j, 1)
ActiveSheet.Range("F4").Cells(i, 2) = ActiveSheet.Range("B4:D13").Cells(j, 2)
ActiveSheet.Range("F4").Cells(i, 3) = ActiveSheet.Range("B4:D13").Cells(j, 3)
End If
Next j
Next i
End Sub
⧭ Output:
Run the code. It’ll create a sorted copy of the data set in a range starting from cell F4, based on the joining dates.
⧭ Notes:
- In lines 16, 17, and 18, we used F4 because we wanted to copy the sorted range in a location starting from cell F4. You can change it according to your wish.
- There is another advantage of using this code. You needn’t sort all the columns of the data set, rather you can sort only some specific columns.
- For example, you can sort only the names and joining dates of the salaries based on the joining dates. To do that, instead of the lines 16, 17, and 18, use:
ActiveSheet.Range("F4").Cells(i, 1) = ActiveSheet.Range("B4:D13").Cells(j, 1)
ActiveSheet.Range("F4").Cells(i, 2) = ActiveSheet.Range("B4:D13").Cells(j, 3)Here we’ve taken columns 1 and 3 from our data set. That’s why we have used Cells(j, 1) and Cells(j, 3).
Read More: Excel VBA to Sort Column Ascending
Download the Practice Workbook
Related Articles
- Excel VBA Sort Named Range
- Excel Macro: Sort Multiple Columns with Dynamic Range
- VBA to Sort Column in Excel
- Excel VBA to Sort by Column Header Name
