Sorting : In Excel Sorting can be done easily manually. But here we are going to learn how to do sorting using Excel VBA/Macro. Mainly sorting can be of two types.

Simple Sorting: Simple sorting is nothing but sorting alphabetically or by number in ascending or descending order.


Sub Sorting()

'---  First Select the Range which you want to sort

Range("I8:L15").Select

'---  Now clear the Sort fields before sorting. This is important otherwise sorting will not take place

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I8"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort

'---  Select the Range on which you want to perform the Sort.
'---  If it includes the header row as well then put .Header=xlYes, else xlNo.

 .SetRange Range("I8:L15")
.Header = xlYes

'---  It is better to put MatchCase as False. In case you want Sorting as Case senstavie then you can make it "True"

.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 
Custom Sorting: In custom sorting we do the sorting on the basis of a list of Values. For example: sorting by Month’s or Day’s Name if we do the simple sorting then, all the Months will be sorted alphabetically and so the order will no longer be valid. For such sorting, we create a custom list in an Ascending or Descending Order, how you want.

Note: CustomOrder can have any number of Values in any Order. Sorting will be done exactly in the same order.For Example:i) For Month SortingCustomOrder:= “Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec”ii) For Some other Custom SortingCustomOrder:= “Passed,Failed,Inquiry,Defect”


Sub Sorting()

'---  First Select the Range which you want to sort 

Range("I8:L15").Select

'---  Now clear the Sort fields before sorting. This is important otherwise sorting will not take place

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

'---  For custom Sorting we need to add the custom list in same order how you want to sort.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I8"), SortOn:=xlSortOnValues, _
Order:=xlAscending, CustomOrder:= "Mon,Tue,Wed,Thu,Fri,Sat,Sun", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort

'---  Select the Range on which you want to perform the Sort. 
'---  If it includes the header row as well then put .Header=xlYes, else xlNo 

.SetRange Range("I8:L15")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!