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 executed
After 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.