Range.Sort method (Excel)
Sorts a range of values.
Syntax
expression.Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
expression A variable that represents a Range object.
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 PivotTable. |
| Type | Optional | Variant | Specifies which type of elements are to be sorted within a PivotTable. Specify xLSortLabels to sort by the labels targeted by the experession Range in the Row/Colum Labels areas of the PivotTable's range, or xLSortValues when Key1 additionally targets a cell in the Values or Sub/Grand Totals areas using R1C1 notation. |
| 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 PivotTable. |
| 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 a non-case-sensitive sort; cannot be used with PivotTables. |
| Orientation | Optional | XlSortOrientation | Specifies if the sort should be by row (default) or column. Set xlSortColumns value to 1 to sort by column. Set xlSortRows value to 2 to sort by row (this is the default value). |
| 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 PivotTable sorting. |
| DataOption2 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key2; does not apply to PivotTable sorting. |
| DataOption3 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key3; does not apply to PivotTable sorting. |
Return value
Variant
Example
This example gets the value of the color of a cell in column A by using the ColorIndex property, and then uses that value to sort the range by color.
Sub ColorSort()
'Set up your variables and turn off screen updating.
Dim iCounter As Integer
Application.ScreenUpdating = False
'For each cell in column A, go through and place the color index value of the cell in column C.
For iCounter = 2 To 55
Cells(iCounter, 3) = _
Cells(iCounter, 1).Interior.ColorIndex
Next iCounter
'Sort the rows based on the data in column C
Range("C1") = "Index"
Columns("A:C").Sort key1:=Range("C2"), _
order1:=xlAscending, header:=xlYes
'Clear out the temporary sorting value in column C, and turn screen updating back on.
Columns(3).ClearContents
Application.ScreenUpdating = True
End Sub
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.