Excel VBA to Sort in Descending Order (6 Examples)

Sorting data in descending order is a common task when working with Excel spreadsheets, and it can be easily accomplished using Excel VBA. In order to sort data in descending order you will need to use the Sort method of the Range object. This method allows you to sort the values in a range of cells in ascending or descending order based on a specified key. Today in this article, I am sharing with you some examples to use Excel VBA to sort in descending order.

In the following, you will find an overview of sorting in descending order with Excel VBA.

Download Practice Workbook

You can download our practice workbook from here for free!

Introduction to Excel VBA Sort

Sorting is a powerful tool that allows you to sort data in your Excel worksheet based on specific criteria. Rearranging data in Excel can help you to better analyze and understand the information in your worksheet, by organizing it in a more logical and meaningful way.

With Excel VBA Sort, you can sort data in ascending or descending order, by one or multiple columns, and with a custom sort order. This can be especially helpful when dealing with large amounts of data that need to be sorted quickly and efficiently.

How to Launch VBA Editor in Excel

VBA(Visual Basic for Applications) provides a user-friendly interface for writing VBA code. There are several ways of launching a VBA editor in Excel. Below I have shared 2 simple ways of launching a VBA editor.

Utilizing Module Tool:

• Open your workbook and select the Visual Basic option from the Developer tab.

• From the new window, choose Module from the Insert tab.

• As a result, a module will be opened where you can write, manage and edit code.

Using View Code Option:

• Simply, choose the View Code option from the Context Menu.

• A window will be opened for the chosen sheet for managing VBA.

Excel VBA to Sort in Descending Order: 6 Examples

In the following, I have shared 6 practical applications of sorting in descending order. Stay tuned!

1. Sorting Single Column with Header

Sorting a single column with header refers to the process of arranging the data in a single column of a table in a particular order based on a specific criterion.

Steps:

• First, open a new module, place the following code and Save the file.

Sub Sort_Single_Column_with_Header()
Choosen_row = Cells(Rows.Count, 1).End(xlUp).Row
Set sortRange = Range("F4:F12" & Choosen_row)
sortRange.Sort Key1:=Range("F4"), Order1:=xlDescending, Header:=xlYes
End Sub

• Second, let’s create a macro button from the Insert tab.

• Then, draw a button and choose Assign Macro from the Advanced tab.

• From the Assign Macro window choose your desired Macro name and press OK.

• Finally, click the button and you will see the table is sorted in descending order.

2. Sorting Single Column Without Header Using Excel VBA

In some cases, you might need to sort a single column without a header. For that, follow these steps below.

Steps:

• Open a new module, type the code, and Run it by pressing the F5 key.

Sub Sort_Single_Column_without_Header()
Choosen_row = Cells(Rows.Count, 1).End(xlUp).Row
Set sortRange = Range("C5:C12" & Choosen_row)
sortRange.Sort Key1:=Range("C5"), Order1:=xlDescending, Header:=xlNo
End Sub

• Finally, the table is sorted in descending order.

3. Sorting Multiple Columns with a Header

If you want you can sort multiple columns too. To do so, follow these steps:

Steps:

• Similarly, create a new module, write the code, and run it by hitting the Run icon.

Sub Sort_Multiple_Columns_with_Header()
Choosen_row = Cells(Rows.Count, 1).End(xlUp).Row
Range("B4:F12" & LastRow).Sort Key1:=Range("C4:C12" & Choosen_row), _
Order1:=xlDescending, Key2:=Range("F4:F12" & Choosen_row), _
Order2:=xlDescending, Header:=xlYes
End Sub

• As a result, you will get to see the whole table is sorted according to the Age and Salary named column in descending order. Simple isn’t it?

4. Sorting Multiple Columns Without a Header

Here I will show you how you can sort multiple columns without headers in descending order.

Steps:

• Just like the previous methods, open a module, put the code, and Run.

Sub Sort_Multiple_Columns_without_Header()
Choosen_row = Cells(Rows.Count, 1).End(xlUp).Row
Range("B5:F12" & LastRow).Sort Key1:=Range("F5:F12" & Choosen_row), _
Order1:=xlDescending, Key2:=Range("C5:C12" & Choosen_row), _
Order2:=xlDescending, Header:=xlNo
End Sub

• Finally, we will have the list sorted for multiple columns without a header.

5. Enabling Double Click Event to Sort Columns

In order to save time and increase efficiency you can try the double-click event to sort columns.

Steps:

• Following the video, place the code for the worksheet and Save it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set selected_Range = Range("B4:F12")
If Not Intersect(Target, selected_Range) Is Nothing Then
Set output = Cells(Target.Row, Target.Column)
selected_Range.Sort Key1:=output, Order1:=xlDescending, Header:=xlYes
Cancel = True
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set selected_Range = Range("B4:F12")

If Not Intersect(Target, selected_Range) Is Nothing Then

Set output = Cells(Target.Row, Target.Column)

Sort Key1:=output, Order1:=xlDescending, Header:=xlYes

Cancel = True

• Then, double-click any header from the list to find your desired sorted result. It’s that simple.

6. Apply Excel VBA for Dynamic Sorting in Descending Order

For better data analysis with real-time feedback, I recommend using the dynamic sorting trick. Dynamic sorting in descending order in Excel VBA is a process of sorting data in a worksheet based on a specified column or range of columns in descending order. This type of sorting is called dynamic because it can be automated and updated easily using VBA code. In Excel VBA, dynamic sorting in descending order can be achieved using the Sort method of the Range object. The Sort method takes various arguments including the SortOrder argument which can be set to xlDescending to indicate that the sort should be done in descending order.

Steps:

• In the same fashion, create a new module, place the code inside the module, and Run it.

Sub Sort_dynamic()
[B4].CurrentRegion.Offset(1).Sort [F5], xlDescending
End Sub

• In conclusion, the table will be sorted within a glimpse of an eye.

How to Sort in Ascending Order with Excel VBA

Sorting in ascending order is a process of organizing data in a particular order, from smallest to largest or from earliest to latest, depending on the data type. Ascending order sorting is a useful tool for organizing and analyzing data, as it helps to identify patterns, trends, and outliers that may not be immediately apparent.

Steps:

• Make a new module, write the below code and Run it.

Sub Sort_in_ascending_order()
Choosen_row = Cells(Rows.Count, 1).End(xlUp).Row
Set sortRange = Range("B5:F12" & Choosen_row)
sortRange.Sort Key1:=Range("F5"), Order1:=xlAscending
End Sub

• As a result, our table is sorted into smallest to largest values for the chosen criterion.

How to Sort Across Multiple Sheets Using Excel VBA

Suppose you have some list of data in multiple worksheets and you want to sort them with a single click. Well, with proper VBA code, you can achieve that in a simple way.

Steps:

• Simply, create a new module, write the code, and Run it.

Sub Sort_Multiple_Sheets()
For Each Worksheet In ThisWorkbook.Worksheets
If Worksheet.Name = "MS-1" Or Worksheet.Name = "MS-2" Then
Worksheet.Range("B5:F12").CurrentRegion.Offset(1).Sort Worksheet.Range("F5"), xlDescending
End If
Next Worksheet
End Sub

• In summary, you will see your table sorted for multiple sheets. It’s that simple.

Things to Remember

• With the Header parameter, you can indicate whether the first row of the range contains column headers or not.
• For the Sort method, the Key1 parameter specifies the column or range to sort by, and the Order1 parameter specifies the sort order.

Frequently Asked Questions

• What does it mean to sort in descending order?

Sorting in descending order means arranging data in a list or range from highest to lowest values.

• Can I sort by multiple columns in descending order?

Yes, you can sort by multiple columns in descending order. To do this, specify multiple key columns in the Sort method.

• Is it possible to sort a range based on a custom function in descending order?

Yes, you can sort a range based on a custom function in descending order. To do this, create a custom function that returns a value that can be used for sorting, and then use that function as the sort key.

Conclusion

In conclusion,  using Excel VBA to sort data in descending order is a useful tool for rearranging data in a range or list based on specific criteria. It allows for sorting by a single column or multiple columns and even allows for custom sorting based on user-defined functions. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.