Excel VBA to Sort in Descending Order (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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:

Visiting Visual Basic option from the Developer tab

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

Choosing Module from the Insert option

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

VBA window for writing and editing code

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

Using View Code Option:

Selecting View Code option from the Advanced options

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

VBA window to write and manage code

  • 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

Overview of 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:

Excel VBA code to sort a column with header in descending order

  • 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
Note: For data range I have chosen data (F4:F12) with header.

Creating a button from Developer tab

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

Choosing Assign Macro option for created button

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

Assigning saved macro for the created button

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

Final output with sorting single column with header

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

2. Sorting Single Column Without Header Using Excel VBA

Overview of Sorting single column without header

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

Steps:

VBA code to sort single column without header

  • 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
Note: Here, I have selected the cell range (C5:C12) without header.

Final result sorting single column without header

  • Finally, the table is sorted in descending order.

Read More: VBA to Sort Table in Excel


3. Sorting Multiple Columns with a Header

Overview of sorting multiple columns with header

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

Steps:

VBA code to sort multiple columns with header

  • 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

Final result sorting multiple columns with header

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

Read More: Excel VBA to Custom Sort


4. Sorting Multiple Columns Without a Header

Overview of sorting multiple columns without header

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

Steps:

VBA code to sort multiple columns without header

  • 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

Final result sorting multiple columns without header

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

Read More: How to Sort Array with Excel VBA


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:

VBA code to enable double click event to sort columns

  • 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

VBA Code Breakdown:

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

: In this part, we have a subroutine of two parameters. Of them, Target indicates the cell that the user double–clicked on. Cancel is a boolean value that can be set to True to cancel the default behavior of the double-click.

Set selected_Range = Range("B4:F12")

: Here, we have a variable named selected _Range and set it to the range of cells from B4 to F12.

If Not Intersect(Target, selected_Range) Is Nothing Then

: In this line it checks whether the Target cells are within the selected_Range of cells. If it is, the code inside the If block will be executed.

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

: This line defines a variable named output that sets the cell value for the double-click pressed.

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

: This line sorts the selected_Range of cells based on the output cell, in descending order. The Header parameter is set to xlYes to indicate that the first row of the range contains headers.

Cancel = True

: This line sets the Cancel parameter to True, which cancels the default behavior of the double-click.

  • 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

Overview of 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:

VBA code for dynamic sorting in descending order

  • 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
Note: The CurrentRegion property is used to identify the range of data that needs to be sorted.

Final output with dynamic sorting in descending order

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

How to Sort in Ascending Order with Excel VBA

Overview of sorting in ascending order

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:

VBA code to sort in ascending order

  • 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

Final output sorting in ascending order

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

Read More: Excel VBA to Sort Alphabetically


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:

VBA code to sort across multiple sheets

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


Download Practice Workbook

You can download our practice workbook from here for free!


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.


Related Articles

What is ExcelDemy?

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

Tags:

Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo