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

excel macro sort multiple columns dynamic range


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

Use the Ascending Order in Excel VBA Macros to Sort Multiple Columns in Dynamic Range

  • 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

Use the Ascending Order in Excel VBA Macros to Sort Multiple Columns in Dynamic Range

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

Use the Ascending Order in Excel VBA Macros to Sort Multiple Columns in Dynamic Range

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

Sort Multiple Columns with Dynamic Range in Descending Order in Excel VBA Macros

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

Sort Multiple Columns with Dynamic Range in Descending Order in Excel VBA Macros

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


Similar Readings


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

Use Multiple Columns to Sort with Header in Excel

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

Use Multiple Columns to Sort with Header in Excel

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.

Sort Multiple Columns With Worksheet Name with Excel Macro

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo