How to Sort Multiple Columns with Excel VBA (3 Methods)

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 the Sort Function of VBA in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Suitable Ways to Sort Multiple Columns with Excel VBA

Here we’ve got a data set with the names, salaries, and joining dates of some employees of a company called Mars Group.

Data set to Sort Multiple Columns with Excel VBA

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

Sort Multiple Columns with the Sort Function of VBA in Excel

⧭ 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 (6 Examples)


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

VBA Code to Sort Multiple Columns with Excel VBA
⧭ 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.

Output to Sort Multiple Columns with Excel VBA

Related Content: How to Sort Multiple Columns in Excel Independently of Each Other


Similar Readings


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

VBA Code to Sort Multiple Columns with Excel VBA

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

VBA Code to Sort Multiple Columns with Excel VBA

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: VBA to Sort Column in Excel (4 Methods) 


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.


Conclusion

Using these methods, you can sort multiple columns with VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our website ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo