VBA Sort Data with headers Excel Example Macro Code

Home/VBA/VBA Sort Data with headers Excel Example Macro Code

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

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

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 with headers in excel file


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

VBA Sort Data with headers in Excel Workbook: Syntax


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


Range.Sort Key1:=Range("A1"), Header:=xlYes

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 Header is to tell excel that your data is having header row.

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, Header:=xlYes

Sort Data with header row in Excel using VBA: Examples


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

Sub sb_VBA_Sort_Data()
Range("A1:D10").Sort _
Key1:=Range("A1"), Header:=xlYes
End Sub

Instructions to run the VBA code to sort the data with headers in Excel Workbook


Please follow the below instructions to execute the VBA code to sort the data with headers in excel workbook.
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 your Data in Excel sheet is sorted based on the Column A.

Explained VBA Code to sort the Excel Data

Starting the program and sub Procedure to write VBA code to sort data with headers in excel.

Sub sbSortData_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”), Header:=xlYes
End Sub
Ending the sub procedure to sort the data with header row.

VBA to Sort the data with header row 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. 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, Header:=xlYes 
End Sub 

Excel VBA to sort data in Descending Order


Here is the example macro to sort a range of data in Descending order. This method will have more properties to customize the sort options.

Sub sbSortDataInExcelInDescendingOrder()
Dim strDataRange, strkeyRange As String
strDataRange = "C1:F6"
strkeyRange = "D2:D6"
With Sheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add _
Key:=Range(strkeyRange), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange Range(strDataRange)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Excel VBA to sort data in Ascending Order


Here is the example macro to sort a range of data in Ascending Order. This method will have more properties to customize the sort options.

Sub sbSortDataInExcelInAscendingOrder()
Dim strDataRange, strkeyRange As String
strDataRange = "C1:F6"
strkeyRange = "D2:D6"
With Sheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add _
Key:=Range(strkeyRange), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange Range(strDataRange)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
LIMITED TIME OFFER
By |April 22nd, 2014|VBA|14 Comments

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

14 Comments

  1. akshay March 10, 2015 at 9:45 AM - Reply

    Sorting in excel with the help of VBA script is amazing. Thanks for the article.

    • PNRao March 21, 2015 at 2:26 PM - Reply

      Thanks Akshay – we are glad to hear such kind of great feedback from our readers. – Thanks-PNRao!

  2. Don Leonard April 21, 2015 at 1:00 AM - Reply

    I have been trying to use excels sort method. Thanks for the code. It work for sorting based on one column.
    I would like to sort using several columns that the excel method permits. Everything I have tried does not work. Any suggestions?

    • R May 14, 2015 at 10:48 PM - Reply

      Haven’t tried this, but I suspect that Key2:=…, Key3:=,,,, etc, is what’ll do the trick

  3. Sohail May 14, 2015 at 9:48 AM - Reply

    Thanks… I need practical example or situation build up for an event etc.. that more fantasy…

    Regards

  4. VIvek Sharma May 28, 2015 at 11:39 PM - Reply

    What’s wrong in here:
    lastrow=426

    Application.Workbooks(“bookname.xlsx”).Worksheets(1).Range(“A2:U” & lastrow).Sort Key1:=Range(“F2:F” & lastrow), _
    Order1:=xlAscending, Header:=xlNo

  5. Zel August 13, 2015 at 10:25 PM - Reply

    Hi, I am blonde but this sorts descending. How do a sort ascending?

    • PNRao August 13, 2015 at 11:40 PM - Reply

      Hi Zel,

      I have updated the post with the examples. Please check the last example to sort data in ascending order.

      Thanks-PNRao!

  6. October 7, 2015 at 6:43 AM - Reply

    Hi PNRao,

    I’m trying to work out a seemingly easy task and, if I had the time and dedication to really get to it, I would probably be able to solve it myself.

    However, due to time constraints and lack of thorough VBA and/or Excel knowledge, I am stuck with the following:

    An excel spread sheet (sheet1) with 1 column (C in my case) with data. Row 1 contains a header, row 2 as well. Row 3 through 655 contain data with the following format:

    1.25L Soft Drinks – 7 Up
    1.25L Soft Drinks – Coke
    1.25L Soft Drinks# – 7 Up
    1.25L Soft Drinks# – Coke

    Basically all the data in column C contains a duplicate, being the only difference the # tag. I would very much like to sort column C in such a way that the products w/o # appear on tab, alphabetically sorted and the products with # appear at the bottom, also alphabetically sorted. A nice to have would be an empty row between the set of data with # and set of data w/o #.

    I am pretty sure that similar codes or other methods have been worked out before but after 3 pretty annoying and time-wasting hours searching the internet my boss really doesn’t want me to get on with it 🙁 I do however HAVE to sort the data. I would have to this multiple times so I am eagerly looking for VBA code.

    Would you have any suggestions on how to code this in VBA?

    Kind regards,

    Tim

  7. Arun January 19, 2016 at 9:21 PM - Reply

    Hi i want to sort date in excel by using VBA .. from list of dates i want to select yesterday date data only.. could u please help

  8. Ambrogio July 20, 2016 at 6:30 AM - Reply

    The article on SORTING in Excel with VBA is EXCELLENT, CONCISE & CLEAR . Thank you very much.

  9. Mayura October 18, 2016 at 6:38 AM - Reply

    Dear team
    I want to sort my data in macro
    But sometimes data records are 500 and sometimes it is more or less than that then what type of condition i have to give to sort and move the data other sheet

    • PNRao October 23, 2016 at 9:32 AM - Reply

      Find the Dynamic Row/ Last Row before sorting the data and set the sort range accordingly. Please refer the below article to find the last row with data:
      http://mongopono.ru/excel-vba/finding-last-used-row-with-data-worksheet/

      Sub sb_VBA_Sort_DynamicData()
      Dim lRow As Long
      lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
      Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
      lRow = lRow - 1
      Loop
      Range("A1:D" &lRow).Sort _
      Key1:=Range("A1"), Header:=xlYes
      End Sub
      

      Thanks-PNRao!

  10. shyam August 3, 2017 at 10:26 AM - Reply

    Hi plz solve this.There is a sequence of numbers in one row

    3,10,8,87,88,89,90,45,76,56,60

    the code must identify the numbers which are in ascending sequence and print it in the next sheet called Sequence 1.

Leave A Comment


Related pages


sort selected cells by last name in ascending orderexcel vba best practicesvba msgbox syntaxoffset meaning in hindivb array lengthconditional if statements in excelexcel stock graphvba create macrohow to unlock an excel file that is password protectedexcel arrange in ascending orderuse of sumif in excelcreate new workbook vbaplot chart worksheetwhat are the shortcut keys in ms excelexcel vba copy worksheetdelete all empty rows in excelword template agendahow to remove blank rows in excel 2007vba sort listboxexcel vba screenupdatingwait excel vbatask tracking excel templatemultiple criteria excelxml file to excel converterexcel chart typesexcel vba userform tutorialadvanced excel test questions and answersexcel vba addressexcel macro sort rangevba rgb color charthyperlink function exceldatabase dmlvlookup function in excel 2013microsoft macros excel tutorialexcel vba loop through sheetswhat is a vlookupadvanced filter in excelexcel vba iferrorhow to open xlsm file in excel 2003vbokonlyunhide worksheetaccess vba connection stringexcel macro create new workbookpivot table refresh vbacommand button click vbawhat does it mean to merge cellsentirerow.deleteexcel activeworkbookscripting.filesystemobject opentextfilecase statements in vbavba pdf to excelhard return in excelvba excel columnsvba row hideexcel hyperlink to folderexcel 2003 to 2013 convertervba message box yes nocase excel vbaaccess vba tutorial pdfddl dml dqlexcel interior colorvba copy row from one sheet to anotherunprotect excel sheet 2013excel vba columns functionvba code to save workbookvba arrays excelpivot table commanduseful vba codems access visual basic commandsexcel macro wait timevba code excellookup formula in excelvlookup vba exceliferror vlookupms access vba examplesvba excel print