SortSpecial Method

Microsoft Excel Visual Basic

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

expression    Required. An expression that returns one of the objects in the Applies To list.

SortMethod   Optional 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’ve selected or installed.

XlSortMethod can be one of these XlSortMethod constants.
xlStroke. Sorting by the quantity of strokes in each character.
xlPinYin default. Phonetic Chinese sort order for characters.

Key1   Optional Variant. 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   Optional XlSortOrder. The sort order for the field or range specified in the Key1 argument.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending default. Sorts Key1 in ascending order.

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

Key2   Optional Variant. 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.

XlSortType can be one of these XlSortType constants.
xlSortLabels. Sorts the PivotTable report by labels.
xlSortValues. Sorts the PivotTable report by values.

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

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key2 in descending order.
xlAscending default. Sorts Key2 in ascending order.

Key3   Optional Variant. 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   Optional XlSortOrder. The sort order for the field or range specified in the Key3 argument. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key3 in descending order.
xlAscending default. Sorts Key3 in ascending order.

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

XlYesNoGuess can be one of these XlYesNoGuess constants.
xlGuess. Lets Microsoft Excel determine whether there’s a header, and to determine where it is, if there is one.
xlNo default. The entire range should be sorted.
xlYes. The entire range should not be sorted.

OrderCustom   Optional Variant. 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   Optional Variant. 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   Optional XlSortOrientation. The sort orientation.

XlSortOrientation can be one of these XlSortOrientation constants.
xlSortRows default. The sort is done by row.
xlSortColumns. The sort is done by column.

DataOption1   Optional XlSortDataOption. Specifies how to sort text in key1. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

DataOption2   Optional XlSortDataOption. Specifies how to sort text in key 2. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

DataOption3   Optional XlSortDataOption. Specifies how to sort text in key 3. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

Remarks

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

Example

This example sorts the range A1:A5 using Pin Yin (phonetic Chinese sort order for characters). In order to sort Chinese characters, this example assumes the user has Chinese language support for Microsoft Excel. Even without Chinese language support, Excel will default to sorting any numbers placed within the specified range for this example. This example assumes there is data contained in the range A1:A5.

Sub SpecialSort()

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

End Sub