Export (0) Print
Expand All
Expand Minimize

Range.Sort Method (Excel)

Sorts a range of values.

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

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

Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.

Concepts

Other resources

Show:
© 2014 Microsoft