VBA Sort Data with No headers Excel Example Macro Code

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

VBA code to sort data with no 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.



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 No 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 No 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:=xNo

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:=xNo

Sort Data with No header row in Excel using VBA: Examples

The following VBA code is to sort the data with No 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:=xNo
End Sub

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

Please follow the below instructions to execute the VBA code to sort the data with No 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 No 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:=xlNo
End Sub
Ending the sub procedure to sort the data with No header row.

VBA to Sort the data with No 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:=xlNo 
End Sub 
Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |April 22nd, 2014|VBA|0 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).

Leave A Comment

Related pages

excel rgb color codeslast cell in excelvb automation errorexcel 2010 multiple if statementspivot table excel 2007 tutorialexcel template schedule plannervba fileopendeveloping dashboardsspell check shortcut in excelhow do you password protect an excel spreadsheetpivot tables exampleshow to merge cells in excel with data in thempivot tables definitionrow count vbanutshell tabsexample of vlookup function in excelvba filedialog selecteditemsvba redim arrayexcel macro tableformula for adding a column in excelactivesheet vbaexcel 2003 macroscopy and paste vbareplace function in excel 2007vba emptyvba code analyzerhow to use userform in excelexcel vba insert into access databasevba arrays excelvb calculator source codeexcel vba create formvba event handlermicrosoft outlook questions and answersrange select vbavba change row colorexcel macro open excel fileexcel password protect worksheetexcel 2007 gantt chart templateexcel for loopsunlock password excelmacros in vbvbscript msgbox examplecalling functions in vbaexcel costing templategantt chart template word 2010excel developer menuword add developer tab to ribbonapplication.filedialog msofiledialogfilepickerhow to remove password protection in excel 2010seriescollection vbaiif excelvba programming courseselect statement vbaexcel vba with selectionexcel interactive dashboard templatefso objectvba data entryvba instr examplehow to unhide column a in excel 2010using sql in excel vbasumif formula in excel 2010 with exampleprotect excel sheetexcel macro close excelbubble chart excel templateglobal variables in vbaadodb connection string access 2010ms excel dashboard templatesexcell remove duplicatesmeeting minute templateunique rows in excelif statements in excel vbaaccess messageboxmultiple vlookup criteriadml examplevbscript message box