Excel VBA Sort Range with Multiple Keys

On day-to-day tasks, you may have to use the Excel VBA to sort range with multiple keys. You can do it quite easily with the help of Excel VBA, as I have done in the earlier video, where I have sorted multiple keys of a range B5:E13 based on a single column in Excel.

Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. To assist users in creating and manipulating data, it provides a wide range of features and operations. The ability to sort range with multiple keys is one of Excel’s useful features. Sorting range is a useful feature in Excel that can be used to visually divide data and make it simpler to read. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences.

Excel comes with a programming language called VBA. Users can automate processes, write original functions, and change data thanks to it. One of the formatting options that may be accessed and changed using Excel VBA is deleting rows based on cell value. Users can easily modify their dataset by deleting rows based on cell value with this attribute. In this article, I will show you various examples and methods to sort ranges with multiple keys in Excel with the help of VBA. Hence, read through the article to learn more and save time.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Getting Started with VBA in Excel

A macro is a sequence of instructions. This sequence of instructions automates some aspects of Excel that you do regularly. When you’re automating some of your work in Excel using macros, you can perform more efficiently and with fewer errors. For example, you may create a macro to format and print your month-end sales report. After the macro is developed, you can then execute the macro to perform many time-consuming procedures automatically.

A user doesn’t have to be a power user to create and use simple VBA macros. Casual users can simply turn on Excel’s macro recorder: Excel will record your actions and convert them into a VBA macro. When you execute this macro, Excel performs the actions again.

1. Enabling Developer Tab in Excel

All of the Office applications have a Developer tab in Excel Ribbon. You may find it hidden usually. You need to enable it through the settings option. We are more focused on the usage and application of Microsoft Excel here. But it is almost the same for any other Office application. However, when you’re planning to work with VBA macros, you’ve to make sure that the Developer tab is present on the Excel Ribbon.

2. Launching VBA Editor

Once you have the Developer tab, you will have access to the VBA editor window that will enable you to add, edit, or remove VBA code. Individual VBA codes are generally stored in a place called a module. It is usually a good practice to store different codes in different modules. Here is how you can create a module in the VBA window.

• Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
• Secondly, click the Insert button and select Module from the menu.

• Thirdly, it will create a module. However, it will look like the one below.

Sort Range with Excel VBA

Sorting data in Excel is a common task, and it can be tedious to manually sort large amounts of data. Before we start, let’s first define what we mean by sorting a range. Sorting a range means rearranging the cells within a range so that the data appears in a specific order. This can be done based on values in a single column or multiple columns. To sort a range using VBA, we need to use the Sort method. The Sort method is a built-in function in Excel VBA that sorts a range of cells based on specified criteria. There are 2 types of sorting available in Excel.

1. Sort Ascending

In Excel, you can sort data in ascending order. Ascending order means that the data is sorted from smallest to largest (A-Z or 1-10). To sort data in ascending order using VBA, you can use the Order1 argument of the Sort method. The Order1 argument specifies the sort order for the first sort key. To sort in ascending order, set Order1 to xlAscending. The following syntax is to sort the range B5:D15 based on the values in column B, in Ascending order.

Range("B5:D15").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlYes

2. Sort Descending

Descending order means that the data is sorted from largest to smallest (Z-A or 10-1). Sorting data in ascending or descending order can be done manually or with VBA. To sort in descending order, set Order1 to xlDescending. The following syntax is to sort the range B5:D15 based on the values in column B, in Descending order.

Range("B5:D15").Sort Key1:=Range("B8"), Order1:=xlDescending, Header:=xlYes

Excel VBA Sort Range with Multiple Keys: 3 Suitable Ways

In this tutorial, I will show you how to sort ranges with multiple keys in Excel VBA. Here, I have used 3 different ways to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains the “Net Sales of Products”. However, it contains the Name of the Emoloyee, residing City, Salary, and Joining Date.

1. Sorting Multiple Keys of a Range Based on Single Column

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 dataset in ascending order according to the Joining Dates of the employees. The process is quite simple. The code below will complete the task.

• Initially, create a new module in the VBA window.
• Then, insert the following code in the module.

'Sorting Multiple Keys of a Range Based on Single Column
'Declaring Sub-Procedure
Sub Sort_multiple_keys_Based_on_Single_Column()
ActiveSheet.Range("B5:E13").Sort Key1:=Range("E8"), Order1:=xlAscending
End Sub

In this code, I have sorted multiple keys of a range B5:E13 in Ascending order based on a single column in Excel. As a sort key, I have used the E8 cell. You can use any cell within the same column which contains data.

• Finally, press the F5 key or the Run button to run the code.

2. Excel VBA to Sort Multiple Keys of a Range Based on Multiple Columns

Now, I will 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. Again, I will use the Sort method of VBA, but this time I’ll use the Key argument and the Order argument twice. So, let’s get started to solve it.

• In the beginning, create a new module in the VBA window.
• Then, insert the following code in the module.

'Sorting Multiple Keys of a Range Based on Multiple Column
'Declaring Sub-Procedure
Sub Sort_multiple_keys_Based_on_Multiple_Column()
ActiveSheet.Range("B5:E13").Sort Key1:=Range("D10"), _
Order1:=xlDescending, key2:=Range("E10"), order2:=xlAscending
End Sub

In this code, I have sorted multiple keys of a range B5:E13 based on multiple columns in Excel. As sort key 1, I have used the D10 cell and E10 cell as sort key 2. Here, I have selected Descending as Order1 and Ascending as Order2. As a result, column D will be sorted in descending order and column E will be sorted in ascending order.

• Finally, press the F5 key or the Run button to run the code.

3. VBA to Sort Multiple Keys of a Range in Different Locations Through Iteration in Excel

Up till now, I’ve sorted the data set in their original location, using the Sort method of VBA. You can create a sorted copy of the dataset in a different location, by iterating through each of the rows. Now, I will show you how to sort the Employee column based on the Joining Date column. However, I will show the output in the same sheet side-by-side.

• Firstly, create a new module in the VBA window.
• Secondly, insert the following code in the module.

'Sorting Through Iteration
'Declaring Sub-Procedure
Sub Sort_through_Iteration()
'Declaring Variables
Dim MyArray() As Variant
MyArray = Application.Transpose(ActiveSheet.Range("D5: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("B5:E13").Rows.Count
        If MyArray(i) = ActiveSheet.Range("B5:E13").Cells(j, 3) Then
           ActiveSheet.Range("G5").Cells(i, 1) = ActiveSheet.Range("B5:E13").Cells(j, 1)
           ActiveSheet.Range("G5").Cells(i, 2) = ActiveSheet.Range("B5:E13").Cells(j, 2)
           ActiveSheet.Range("G5").Cells(i, 3) = ActiveSheet.Range("B5:E13").Cells(j, 3)
        End If
    Next j
Next i
End Sub

In this code, I’ll convert the joining dates (D4:D13)  to a one-dimensional Array. Then I will iterate through the For loop to sort the array in Ascending order. Afterward, I will sort the data set based on the Ascending order of the Joining Dates, in a different location with the help of the If statement and For loop. Here, I have selected the initial cell as G5 of the same sheet.

• Thirdly, press the F5 key or the Run button to run the code and the output will appear as below.

How to Change Sort Orientation in Excel VBA

In Excel, you may have thousands of rows in your worksheet that are necessary to sort so that you can analyze your data. You can use Excel VBA to change sort orientation automatically.

• Initially, create a new module in the VBA window.
• Then, insert the following code in the module.

'Changing Sort Orientation
'Declaring Sub-Procedure
Sub Change_Sort_Orientation()
'Provide Range to change sort
Range("B5:E13").Sort Key1:=Range("B5"), _
                     Order1:=xlAscending, _
                     Orientation:=xlSortRows
End Sub

In this code, I have changed the sorting of a range B5:E13 in Ascending order based on rows in Excel. As a sort key, I have used the B5 cell.

• Finally, press the F5 key or the Run button to run the code and you will get your desired output.

VBA Multiple Sort Keys in Different Rows

Suppose, you need to sort keys in multiple rows. Then VBA helps you to do so with just one click. The process is quite simple and easy to operate. In this part, I will use 4 different keys to sort in ascending order in different rows. Hence, read through the rest of the part to learn more.

• Initially, create a new module in the VBA window.
• Then, insert the following code in the module.

'Sorting Multiple Keys in Different Rows
'Declaring Sub-Procedure
Sub Sort_Multiple_Keys_in_Different_Rows()
    With ActiveSheet.Sort
        .SortFields.Clear
        'Provide Range and Sort order
        .SortFields.Add Key:=Range("B5:B13"), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=Range("C5:C13"), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=Range("D5:D13"), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=Range("E5:E13"), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange Range("B5:E13")
        .Apply
    End With
End Sub

• Next, press the F5 key or the Run button to run the code and you will receive your desired result.

Things to Remember

• You can change the Range in each code to change the location where you want to apply the code.
• You should give the Column number according to your dataset. This number indicates the column from which you want to search for a specific cell value.
• If you want to sort data using double click on the header, you have to put the code from the View Code option of the sheet.
• However, you can change the code according to your preference.
• You can sort by ascending or descending order or both of them at a time in a particular dataset.

Conclusion

These are all the steps you can follow with Excel VBA to sort range with multiple keys. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples. Here, I have demonstrated several situations to sort range which includes sorting multiple keys based on a single column, based on multiple columns, and in a different location through iteration. Moreover, you can easily start your journey with VBA and change sort orientation with the help of VBA. Additionally, I have shown how to sort multiple keys in different rows.

However, you will be able to sort data using a double click on the header in the later part of the article. Hopefully, you can now easily make the needed adjustments and use the codes according to your needs.

I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.