Edit

Range.Sort method (Excel)

  • Article

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.