Excel's VBA Sort Methods
For the most advanced techniques in sorting and searching, Rich's newest book provides a bonus app that provides the code to enhance your most demanding applications.
Power-Up Using Excel VBA Sorts and Searches
Excel offers several ways that a standard sort can be initiated from VBA. The code below illustrates the earlier method used in Excel 2003 and the more current method(s) used in Excel 2007 and 2010.
All of the sort methods demonstrated below function the same. The syntax demonstrates variable ways to accomplish the same sorting goal.
Program Code
Option Explicit
Sub DemonstrateSortTypes()
' *****************************************************
' Workbook and Worksheet Variables
' *****************************************************
Dim wkbTemplate As Workbook
Dim wksDivisionAssignments As Worksheet
' *****************************************************
' Other Variables
' *****************************************************
Dim lngNumberOfRowsInDivAssignments As Long
Dim rngDivisionSortRange As Range
' *********************************************
' Set Up Workbook and Worksheet Variables
' *********************************************
Set wkbTemplate = ActiveWorkbook
Set wksDivisionAssignments = wkbTemplate.Sheets("DivisionAssignments")
' ***********************************************
' Turn Off Screen Updating
' ***********************************************
Application.ScreenUpdating = False
' ********************************************************
' Count The Number Of Rows To Be Sorted
' ****************************** **************************
lngNumberOfRowsInDivAssignments = wksDivisionAssignments.Cells(Rows.Count, "A").End(xlUp).Row
' *************************************************************
' Set the Range for the Sort of Columns 1 - 3 Starting At Row 3
' *************************************************************
Set rngDivisionSortRange = Range(wksDivisionAssignments.Cells(3, 1), wksDivisionAssignments.Cells(lngNumberOfRowsInDivAssignments, 3))
' ********************************************************
' Excel 2003 Method
' ********************************************************
rngDivisionSortRange.Sort Key1:=wksDivisionAssignments.Range("A3"), Order1:=xlAscending, _
Key2:=wksDivisionAssignments.Range("B4"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
' ********************************************************
' Excel 2007-2010 Sort Method Using Add Key Column
' ********************************************************
With wksDivisionAssignments.Sort
.SortFields.Clear
.SortFields.Add Key:= _
Columns("A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
.SortFields.Add Key:= _
Columns("B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
.SetRange rngDivisionSortRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
' ********************************************************
' Excel 2007-2010 Sort Method Using Add Key Range
' ********************************************************
With wksDivisionAssignments.Sort
.SortFields.Clear
.SortFields.Add Key:= _
Range("A3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
.SortFields.Add Key:= _
Range("B3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
.SetRange rngDivisionSortRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
wksDivisionAssignments.Select
wksDivisionAssignments.Range("A1").Select
' ***********************************************
' Turn On Screen Updating
' ***********************************************
Application.ScreenUpdating = True
End Sub
' *************************************************************************************
' Using Excel 2003 With The Columm Key Feature
' *************************************************************************************
Public Sub SortColumnA()
' *****************************************
' Assign Column A Sort Button To This Macro
' *****************************************
Call SortColumns("A")
End Sub
Public Sub SortColumns(strColumnToSort As String)
' ********************************************
' Assume 4 columns starting in column A
' ********************************************
' *****************************************************
' Workbook and Worksheet Variables
' *****************************************************
Dim wkbMainWorkbook As Workbook
Dim wksMainWorksheet As Worksheet
Dim strColumn As String
' *****************************************************
' Other Variables
' *****************************************************
Dim lngNumberOfRowsInMainWorksheet As Long
Dim rngSortRange As Range
' *********************************************
' Set Up Workbook and Worksheet Variables
' *********************************************
Set wkbMainWorkbook = ThisWorkbook
Set wksMainWorksheet = wkbMainWorkbook.ActiveSheet
' ***********************************************
' Turn Off Screen Updating
' ***********************************************
Application.ScreenUpdating = False
' ********************************************************
' Count The Number Of Rows To Be Sorted
' ****************************** **************************
lngNumberOfRowsInMainWorksheet = wksMainWorksheet.Cells(Rows.Count, "A").End(xlUp).Row
' *************************************************************
' Set the Range for the Sort of Columns 1 - 4 Starting At Row 3
' *************************************************************
Set rngSortRange = Range(wksMainWorksheet.Cells(3, 1), wksMainWorksheet.Cells(lngNumberOfRowsInMainWorksheet, 4))
' ********************************************************
' Excel 2003 Method
' ********************************************************
rngSortRange.Sort Key1:=wksMainWorksheet.Columns(strColumnToSort), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption2:=xlSortNormal
' ***********************************************
' Turn On Screen Updating
' ***********************************************
Application.ScreenUpdating = True
End Sub