In this article, I’ll show you how you can sort a data set with multiple columns with VBA in Excel. You’ll learn to sort multiple columns of a data set based on a single column, as well as multiple columns, using both the Sort method of VBA and iterating manually.
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
Here we’ve got a data set with the names, salaries, and joining dates of some employees of a company called Mars Group.
Our objective today is to sort the multiple columns of this data set with Excel VBA.
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 according to the ascending order of the joining dates of the employees.
Read More: How to Sort Range Using VBA in Excel
2. Sort Multiple Columns of a Range based on Multiple Columns with VBA in Excel
Now, we’ll sort multiple columns of a range based on multiple columns.
Let’s try to 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 based on the descending order of the salaries, then on the ascending order of the joining dates.
Read More: Excel VBA Sort Range with Multiple Keys
3. Sort Multiple Columns of a Range in a Different Location through Iteration with VBA in Excel
Up till now, we’ve sorted the data set in their original location, using the Sort method of VBA.
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.
First, we’ll convert the joining dates (D4:D13) to a one-dimensional Array.
Dim MyArray() As Variant
MyArray = Application.Transpose(ActiveSheet.Range("D4:D13"))
Then we’ll 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
Then we’ll sort the data set based on 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
So 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
Summary
From today’s discussion, we can come to the conclusion that to sort all the columns of a data set based on single or multiple columns, it’s better to use the Sort method of VBA. But to sort not all the columns but some specific columns, you should use the iteration method.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Using these methods, you can sort multiple columns with VBA in Excel. Do you have any questions? Feel free to ask us in the comment section below.
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
