Sort Method

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.  

Sorts a PivotTable report, a range, or the active region if the specified range contains only one cell.

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

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

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

XlSortOrder

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

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 Pivot Table reports.

Variant

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

XlSortOrder

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key2 in descending order.
xlAscendingdefault. 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.

XlSortOrder

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

XlYesNoGuess

XlYesNoGuess can be one of these XlYesNoGuess constants.
xlGuess. Let Microsoft Excel determine whether there’s a header, and to determine where it is, if there is one.
xlNodefault. (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, a normal sort 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.

XlSortOrientation

XlSortOrientation can be one of these XlSortOrientation constants.
xlSortRowsdefault. Sorts by row.
xlSortColumns. Sorts by column.

XlSortMethod

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

XlSortDataOption

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treat text as numeric data for the sort.
xlSortNormaldefault. Sorts numeric and text data separately.

XlSortDataOption

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

XlSortDataOption

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

Remarks

The settings for Header, Order1, Order2, Order3, OrderCustom, and Orientation are saved, for the particular worksheet, each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used.  Set these arguments explicitly each time you use Sort method, if you choose not to use the saved values.

Text strings which are not convertible to numeric data are sorted normally.

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:C20 on Sheet1, using cell A1 as the first sort key and cell B1 as the second sort key. The sort is done in ascending order by row, and there are no headers.  This example assumes there is data in the range A1:C20.

  Sub SortRange1()
      Worksheets("Sheet1").Range("A1:C20").Sort _
        Key1:=Worksheets("Sheet1").Range("A1"), _
        Key2:=Worksheets("Sheet1").Range("B1")
  End Sub

This example sorts the region that contains cell A1 (the active region) on Sheet1, sorting by the data in the first column and automatically using a header row if one exists. This example assumes there is data in the active region, which includes cell A1. The Sort method determines the active region automatically.

  Sub SortRange2()
      Worksheets("Sheet1").Range("A1").Sort _
        Key1:=Worksheets("Sheet1").Columns("A"), _
        Header:=xlGuess
  End Sub