VBA Sort Data Descending Order Excel Example Macro Code

Home/VBA/VBA Sort Data Descending Order Excel Example Macro Code

VBA code to sort data in Descending Order example will help us to sort data in excel worksheets in Descending order. We can use Sort method of Excel Range to sort the data and specify the Sort Order as Descending. In this example we will see how to sort data in Descending Order using VBA. VBA code for sorting data in Descending Order macro should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

VBA code to sort data in Descending Order

Here is the Example VBA syntax and Example VBA Macro code to sort the Excel Data in Descending Order. This will help you to know how to sort data in Excel worksheets using VBA in Descending Order.

VBA Sort Data in Descending Order: Syntax

Following is the VBA Syntax and sample VBA code to Sort the Data in Descending Order using VBA. We are using the Sort method of the Excel Workbook Range object and sort order as Descending.

Range.Sort Key1:=Range("A1"), Order1:=xlDescending

Here you you can set your range into an object or you can directly use Range object like Range(“A1:D100”). And Key1 will be your Sorting Column which you wants to sort by. And we can specify the sort order using Order1.

Here is VBA code to sort the data in Excel by setting the range to an object:

Dim strDataRange As Range
Dim keyRange As Range
Set strDataRange = Range("Your Data Range")
Set keyRange = Range("Your Sort by Column")
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending

Sort Data in Descending using VBA : Examples

The following VBA code is to sort the data in Descending in Excel Worksheet. This code will sort the data in Range A1 to D10 based on the First Column i.e.; A1. And in Descending order.

Sub sb_VBA_Sort_Data_Descending()
Range("A1:D10").Sort _
Key1:=Range("A1"), Order1:=xlDescending
End Sub

Instructions to run the VBA code to sort data in Excel Workbook in Descending Order

Please follow the below instructions to execute the VBA code to sort the excel file.
Step 1: Open any existing Excel workbook
Step 2: Enter some data in A1 to D10
Step 3: Press Alt+F11 – This will open the VBA Editor
Step 4: Insert a code module from then insert menu
Step 5: Copy the above code to sort the data in excel and paste in the code module which have inserted in the above step
Step 5: Now press F5 to execute the code

Now you can observe that the Data in Excel sheet is sorted in Descending order based on the Column A.

Explained VBA Code to Sort the Excel Data in Descending order :

Starting the program and sub Procedure to write VBA code to sort data in Descending order.
Sub sbSortData_Descending _VBA_C()
‘Here Range(“A1:D10”) is target range to sort
‘And Range(“A1”) is the sort key to Sort by
Range(“A1:D10”).Sort _
Key1:=Range(“A1”) _
End Sub
Ending the sub procedure to sort the data in Descending order.

VBA to Sort the data in Descending by assigning to an Object: Examples

It is best practice to assign our target range and key Cell to temporary range objects and then sort the data in Descending order. Here is the simple example to sort the data in Excel using Objects in VBA.

Sub sbSortDataInExcel()
'Delcaring the strDataRange as range store the target range to sort
Dim strDataRange As Range
'Delcaring the keyRange as range store the Sort key range to sort by
Dim keyRange As Range
'Assigning the target sort Range to strDataRange
Set strDataRange = Range("A1:D10")
'Assigning the sort key Range to keyRange
Set keyRange = Range("A1")
'Sorting the data using range objects and Sort method
strDataRange.Sort Key1:=keyRange, Order1:=xlDescending
End Sub 
By |April 22nd, 2014|VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. algae October 19, 2015 at 10:06 PM - Reply

    hello PNRao,
    need a bit of help with your macro for sorting data in descending order.
    My excel file has 20 pages, how can i sort the data on each page with your macro?( rather than doing it sheet by sheet as i do presently.)
    the number of rows is different for some of the sheets. No of col is 9.
    Key range would be A1

    thank you so much

Leave A Comment

Related pages

excel hyperlink to sheetcreate pivottablevlookup tutorial for beginnersexcel copy row to another sheethow to do a vlookup for dummiescreate a pivot table in excel 2007examples of macros in exceltesting tools interview questions and answers for freshersbasic questions in interview for freshersupper case excelexport from excel to access vbaunprotect password protected excel sheetms excel vlookup functionexcel vba alertsubstitute in vbavba userform tutorialsave xlsm as xlsxexcel advanced filter vbaquality checklist template excelexcel vba sql query access databasevbokonly exampledynamic cell reference exceldelete worksheet vbafind duplicate email addresses in excelvba errorexcel hide tabscolor by code worksheetsexcel formula codesms excel formulas with examples pdf in hindiwhere do i find a tick in excelexcel merge columnremove hyperlink excelexcel project gantt chart templatehow to remove duplicate data in excel 2010length of array vbapassword protect xlsword userform examplesexcel developer tab 2013dml and ddl in sqlexcel macro vbainterview question and answer for fresherunprotect sheet forgot passwordbreak protected sheet password excelexcel chart wizard 2010vba workbook objectvba msgbox yes no examplecombobox in excel vbadashboard in excel tutorialsort excel vbaapplication.workbooks.openexcel spreadsheets for beginnersexcel shortcut insert commentmsgbox iconsms excel vlookup tutorialvbscript examples programsvba while loop examplehow to use count function in excel 2007vba unprotect worksheetexcel vba rowsascending order worksheetsvb6 connect to sql serverpivot table in excel 2007 with example xlshow to remove blank rows excelhow to change the width of a column in excelmicrosoft excel basics tutorialsas basics tutorialsvba advanced programmingproject management excel trackerexcel loopingsorting formula in excelexcel formula hlookup exampleexcel developer tab 2013excel to kml macrovanilla foldersduplicate cell excel