Sort/Order related procedures in class Range (Excel VBA)

This page presents procedures from class Range related to the theme Sort/Order: ReadingOrder, Sort and SortSpecial

ReadingOrder

Returns or sets the reading order for the specified object. Can be one of the following XlReadingOrder constants: xlRTL (right-to-left), xlLTR (left-to-right), or xlContext.

ActiveCell.ReadingOrder =

Sort

Sorts a range of values.

Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3, SubField1)

   Range("C1") = "Index"
   Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, header:=xlYes

Arguments

The following arguments are optional

Key1 (Range) - Specifies the first sort field, either as a range name (String) or Range object; determines the values to be sorted.

Order1 (XlSortOrder) - Determines the sort order for the values specified in Key1.

Possible Values are xlAscending - Sorts the specified field in ascending order. This is the default value, xlDescending - Sorts the specified field in descending order.

Key2 - Second sort field; cannot be used when sorting a PivotTable

Type - Specifies which elements are to be sorted

Order2 (XlSortOrder) - Determines the sort order for the values specified in Key2.

Possible Values are xlAscending - Sorts the specified field in ascending order. This is the default value, xlDescending - Sorts the specified field in descending order.

Key3 - Third sort field; cannot be used when sorting a PivotTable

Order3 (XlSortOrder) - Determines the sort order for the values specified in Key3.

Possible Values are xlAscending - Sorts the specified field in ascending order. This is the default value, xlDescending - Sorts the specified field in descending order.

Header (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.

Possible Values are xlGuess - Excel determines whether there is a header, and where it is, if there is one, xlNo - Default. The entire range should be sorted, xlYes - The entire range should not be sorted.

OrderCustom - Specifies a one-based integer offset into the list of custom sort orders

MatchCase (Boolean) - Set to True to perform a case-sensitive sort, False to perform a non-case-sensitive sort; cannot be used with PivotTables.

Orientation (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).

Possible Values are xlSortColumns - Sorts by column, xlSortRows - Sorts by row. This is the default value.

SortMethod (XlSortMethod) - Specifies the sort method.

Possible Values are xlPinYin - Phonetic Chinese sort order for characters. This is the default value, xlStroke - Sort by the quantity of strokes in each character.

DataOption1 (XlSortDataOption) - Specifies how to sort text in the range specified in Key1; does not apply to PivotTable sorting.

Possible Values are xlSortNormal - default. Sorts numeric and text data separately, xlSortTextAsNumbers - Treat text as numeric data for the sort.

DataOption2 (XlSortDataOption) - Specifies how to sort text in the range specified in Key2; does not apply to PivotTable sorting.

Possible Values are xlSortNormal - default. Sorts numeric and text data separately, xlSortTextAsNumbers - Treat text as numeric data for the sort.

DataOption3 (XlSortDataOption) - Specifies how to sort text in the range specified in Key3; does not apply to PivotTable sorting.

Possible Values are xlSortNormal - default. Sorts numeric and text data separately, xlSortTextAsNumbers - Treat text as numeric data for the sort.

SubField1

SortSpecial

Uses East Asian sorting methods to sort the range, a PivotTable report, or uses the method for the active region if the range contains only one cell. For example, Japanese sorts in the order of the Kana syllabary.

If no arguments are defined with the Sort method, Microsoft Excel will sort the selection chosen to be sorted in ascending order.

SortSpecial (SortMethod, Key1, Order1, Type, Key2, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, DataOption1, DataOption2, DataOption3)

Application.Range("A1:A5").SortSpecial SortMethod:=xlPinYin

Arguments

The following arguments are optional

SortMethod (XlSortMethod) - The type of sort. Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that you have selected or installed.

Possible Values are xlPinYin - Phonetic Chinese sort order for characters. This is the default value, xlStroke - Sort by the quantity of strokes in each character.

Key1 (Range) - The first sort field, as either text (a PivotTable field or range name) or a Range object ("Dept" or Cells(1, 1), for example).

Order1 (XlSortOrder) - The sort order for the field or range specified in the Key1 argument.

Possible Values are xlAscending - Sorts the specified field in ascending order. This is the default value, xlDescending - Sorts the specified field in descending order.

Type - Specifies which elements are to be sorted. Use this argument only when sorting PivotTable reports

Key2 (Range) - The second sort field, as either text (a PivotTable field or range name) or a Range object. If you omit this argument, there's no second sort field. Cannot be used when sorting PivotTable reports.

Order2 (XlSortOrder) - The sort order for the field or range specified in the Key2 argument. Cannot be used when sorting PivotTable reports.

Possible Values are xlAscending - Sorts the specified field in ascending order. This is the default value, xlDescending - Sorts the specified field in descending order.

Key3 (Range) - The third sort field, as either text (a range name) or a Range object. If you omit this argument, there's no third sort field. Cannot be used when sorting PivotTable reports.

Order3 (XlSortOrder) - The sort order for the field or range specified in the Key3 argument. Cannot be used when sorting PivotTable reports.

Possible Values are xlAscending - Sorts the specified field in ascending order. This is the default value, xlDescending - Sorts the specified field in descending order.

Header (XlYesNoGuess) - Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.

Possible Values are xlGuess - Excel determines whether there is a header, and where it is, if there is one, xlNo - Default. The entire range should be sorted, xlYes - The entire range should not be sorted.

OrderCustom - This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom, (normal sort order) is used

MatchCase (Boolean) - True to do a case-sensitive sort; False to do a sort that's not case-sensitive. Cannot be used when sorting PivotTable reports.

Orientation (XlSortOrientation) - The sort orientation.

Possible Values are xlSortColumns - Sorts by column, xlSortRows - Sorts by row. This is the default value.

DataOption1 (XlSortDataOption) - Specifies how to sort text in Key1. Cannot be used when sorting PivotTable reports.

Possible Values are xlSortNormal - default. Sorts numeric and text data separately, xlSortTextAsNumbers - Treat text as numeric data for the sort.

DataOption2 (XlSortDataOption) - Specifies how to sort text in Key2. Cannot be used when sorting PivotTable reports.

Possible Values are xlSortNormal - default. Sorts numeric and text data separately, xlSortTextAsNumbers - Treat text as numeric data for the sort.

DataOption3 (XlSortDataOption) - Specifies how to sort text in Key3. Cannot be used when sorting PivotTable reports.

Possible Values are xlSortNormal - default. Sorts numeric and text data separately, xlSortTextAsNumbers - Treat text as numeric data for the sort.