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 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. |
Return Value
Variant
Example
Sample code provided by: Holy Macro! Books, Holy Macro! It’s 2,500 Excel VBA Examples | About the Contributor
This example gets the value of the color of a cell in column A 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
About the Contributor
Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.