How to use VBA Range.Sort Method?

This method is used to dynamically sort any particular columns in Microsoft Excel through VBA program. It depends on which columns you want to consider for sorting function and what specific cell you want to use as key or reference for sorting the data.

Syntax – expression .Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

Range object represents a cell, row, column, selection of cells containing more than one blocks of cells.

Parameters

Name Required/Optional Data Type Description
Key1 Optional Variant Specifies the first sort field, either as a range name (String) or Range object; determines the values to be sorted.
Order1 Optional XlSortOrder Determines the sort order for the values specified in Key1.
Key2 Optional Variant Second sort field; cannot be used when sorting a pivot table.
Type Optional Variant Specified which elements are to be sorted.
Order2 Optional XlSortOrder Determines the sort order for the values specified in Key2.
Key3 Optional Variant Third sort field; cannot be used when sorting a pivot table.
Order3 Optional XlSortOrder Determines the sort order for the values specified in Key3.
Header Optional XlYesNoGuess Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header.
OrderCustom Optional Variant Specifies a one-based integer offset into the list of custom sort orders.
MatchCase Optional Variant Set to True to perform a case-sensitive sort, False to perform non-case sensitive sort; cannot be used with pivot tables.
Orientation Optional XlSortOrientation Specifies if the sort should be in acending or decending order.
SortMethod Optional XlSortMethod Specifies the sort method.
DataOption1 Optional XlSortDataOption Specifies how to sort text in the range specified in Key1; does not apply to pivot table sorting.
DataOption2 Optional XlSortDataOption Specifies how to sort text in the range specified in Key2; does not apply to pivot table sorting.
DataOption3 Optional XlSortDataOption Specifies how to sort text in the range specified in Key3; does not apply to pivot table sorting.


Example:

The following code will sort the data based on column labeled “Agent”. Please see figure below for the result before and after executing the sort method:


Sub Range_Sort()

Dim oneRange As Range

Dim aCell As Range

Set sortRange = Range("F10:H15")

Set keyCell = Range("G10")

sortRange.Sort Key1:=keyCell, Order1:=xlAscending, Header:=xlYes

End Sub

Before sort method is executedAfter sort method is executed (Highlighted in yellow indicates that the “Agent” column has been sorted)

Another example below shows how to sort a column by color index:

Sub Range_Sort_Color()

‘Variable declaration

Dim iCount As Integer

‘Set the color index of column “F” from row 3 to 7 to column “H” row 3 to 7.

For iCount = 3 To 7

Cells(iCount, 8) = _

Cells(iCount, 6).Interior.ColorIndex

Next iCount

‘Apply the sort key defined as column H3 where the color index was stored

Columns("F:I").Sort key1:=Range("H3"), _

order1:=xlAscending, Header:=xlYes

End Sub

Before sort method is executed

After sort method is executed (The following figure shows the corresponding color index and the result after execution)

Depending on the data that you want to sort with different conditions, the sort method is always useful for dynamic manipulation.

Leave a Reply

Your email address will not be published. Required fields are marked *