Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)

While working with large Microsoft Excel, sometimes we need to sort multiple columns with a dynamic range. We can easily do that by using the VBA Macros. Applying a VBA code to sort multiple columns is an easy task. Today, in this article, we’ll learn four quick and suitable ways how Excel macro sort multiple columns dynamic range effectively with appropriate illustrations.

Excel Macro to Sort Multiple Columns with Dynamic Range (Quick View)

Sub Sort_Multiple_Columns_Dynamic_Range()
With ActiveSheet.Sort
.SortFields.Add Key:=Range("B4"), Order:=xlAscending
.SortFields.Add Key:=Range("C4"), Order:=xlAscending
.SetRange Range("B4:E14")
.Header = xlYes
.Apply
End With
End Sub

Download Practice Workbook

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

4 Suitable Ways to Sort Multiple Columns in Dynamic Range with Excel Macro

Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The Name of the sales representatives, their Identification Number, type of products, and the revenue earned by the sales representatives are given in columns B, C, D, and E respectively. From our dataset, we will sort multiple columns with the dynamic ranges using VBA Code. To do that, we will apply the Ascending order, Descending order, and Multiple Columns with the Header Command in VBA Code. Here’s an overview of the dataset for today’s task.

1. Use the Ascending Order in Excel Macro to Sort Multiple Columns in Dynamic Range

Now I’ll show how to sort multiple columns in ascending order by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will sort our data according to the Name of sales representatives in ascending order. Let’s follow the instructions below to sort multiple columns in ascending order!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Sort Multiple Columns will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the Sort Multiple Columns module pops up. In the Sort Multiple Columns module, write down the below VBA

Sub Sort_with_Ascending_Order()
Range("B4:E14").Sort key1:=[B5], order1:=xlAscending, _
key2:=[C5], order2:=xlAscending, Header:=xlYes
End Sub

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the VBA Code, you will be able to sort columns in ascending order which has been given in the below screenshot.

Read More: How to Use Range with Variable Row and Column with Excel VBA

2. Sort Multiple Columns with Dynamic Range in Descending Order with Excel Macro

Now, from our dataset, we will sort multiple columns in descending order. Undoubtedly, this is an easy and time-saving task. Let’s follow the steps below to sort multiple columns in descending order!

Step 1:

• According to method 1, insert a new module and type the below VBA code to sort multiple columns in descending order according to the Identification Number of the sales representatives. The VBA code is,

Sub Sort_with_Descending_Order()
Range("B4:E14").Sort key1:=[C4], order1:=xlDescending, _
key2:=[C4], order2:=xlDescending, Header:=xlYes
End Sub

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• While running the code, you will be able to sort column C in descending order which has been given in the below screenshot.

Read More: VBA to Loop Through Rows in Range in Excel (6 Examples)

Similar Readings

• How to Use VBA for Each Row in a Range in Excel
• How to Use VBA to Select Range from Active Cell in Excel (3 Methods)
• Excel VBA to Loop through Range until Empty Cell (4 Examples)
• How to Convert Range to Array in Excel VBA (3 Ways)

3. Use Multiple Columns to Sort with Header with Excel Macro

In this method, we will sort multiple columns with the header. From our dataset, we will sort our data according to the Name of the sales representatives, and their revenue earned in ascending order. Let’s follow the instructions below to sort multiple columns with the header!

Step 1:

• First, insert a new module according to method 1 and type the below VBA code to change the font color. The VBA code is,

Sub Sort_Multiple_Columns_Dynamic_Range()
With ActiveSheet.Sort
.SortFields.Add Key:=Range("B4"), Order:=xlAscending
.SortFields.Add Key:=Range("E4"), Order:=xlAscending
.SetRange Range("B4:E14")
.Header = xlYes
.Apply
End With
End Sub

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• After running the VBA Code, you will be able to sort our data according to the Name of the sales representatives, and their revenue earned in ascending order which has been given in the below screenshot.

Read More: VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)

4. Excel Macro to Sort Multiple Columns with Worksheet Name

Last but not the least, you can also use your data to sort multiple columns according to the Worksheet Name using the VBA code in Excel. This is an easy task and time-saving also. Let’s follow the instructions below to sort multiple columns according to the Worksheet Name!

Step 1:

• First, insert a new module according to method 1 and type the below VBA code to change the font color. The VBA code is,

Sub Sort_Multiple_Columns_Worksheet_Name()
Rows("4:14").Select
ActiveWorkbook.Worksheets("Worksheet Name").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Worksheet Name").Sort.SortFields.Add Key:=Range("B4:B14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Worksheet Name").Sort
.SetRange Range("B4:E14")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• After running the VBA Code, you will be able tosort multiple columns according to the Worksheet Name which has been given in the below screenshot.

Read More: Excel VBA: Copy Dynamic Range to Another Workbook

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

Conclusion

I hope all of the suitable methods mentioned above to sort multiple columns with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• Excel VBA to Select Used Range in Column (8 Examples)
• Excel VBA: Get Range of Cells with Values (7 Examples)
• How to Use VBA to Set a Range Variable to Selection in Excel (5 Methods)
• How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)
• Excel VBA: Loop Through Columns in Range (5 Examples)