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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags: 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 Advanced Excel Exercises with Solutions PDF  