Excel Range Sort Method to sort data

This Excel tutorial explains how to sort Excel data using Excel VBA Sort Method, and how to sort dynamic Range.

You may also want to read

Excel Table AutoFilter sorting

Excel Worksheet AutoFilter sorting

Excel Range Sort Method to sort data

There are mainly three kinds of sorting in Excel.

1 – Table AutoFilter sorting, using List Object to access AutoFilter.

2 – Worksheet Autofilter sorting, using Range to access AutoFilter.

3 – Use sort Function as seen below, which is a Range Method in VBA.

Syntax of Excel Range Sort Method

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

Example of Excel Range Sort Method using dynamic range

Although there are so many arguments in Range Sort Method,  you only need to use four arguments for basic sorting:  Key, Order, Orientation, Header.

Because you will need to define a Range for Sort Method but the number of rows may vary, the below example defines the last row in column A as lastRow variable.

The below example shows how to sort column A and B, with column A in ascending order and column B in descending order.

Public Sub test_sort()
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A1:B" & lastRow)
    With Rng
        .Sort Key1:=.Range("A1"), Order1:=xlAscending, _
              Key2:=.Range("B1"), Order2:=xlAscending, Orientation:=xlSortColumns, _
              Header:=xlYes
    End With
End Sub

If you read carefully the description of argument Key1, it says “first sort field”. Some people interpret it is as the first Cell to sort, but according to my testing, if you input a Range such as A2, Excel only takes the column letter you input, but neglect the row number, therefore you can type A2, A3 or even A10000.

Outbound References

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.sort.aspx

 

Excel Range Sort Method to sort data

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. xlAscending (default) xlDescending
Key2	Optional	Variant	Second sort field; cannot be used when sorting a pivot table.
Type	Optional	Variant	Specified whether to sort Label or Values when sorting Pivot Report. xlSortLabels xlSortValues
Order2	Optional	XlSortOrder	Determines the sort order for the values specified in Key2. xlAscending (default) xlDescending
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. xlAscending (default) xlDescending
Header	Optional	XlYesNoGuess	Specifies whether the first row contains header information. XlNo (Default) No header XlYes Has header XlGuess Guess has header or not
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 by row or column xlSortRows (default) xlSortColumns
SortMethod	Optional	XlSortMethod	Specifies the sort method of Chinese xlStroke Sorting by the quantity of strokes in each character xlPinYin (default) Phonetic Chinese sort order for characters
DataOption1	Optional	XlSortDataOption	Specifies how to sort text in the range specified in Key1; does not apply to pivot table sorting. 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 the range specified in Key2; does not apply to pivot table sorting. 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 the range specified in Key3; does not apply to pivot table sorting. xlSortTextAsNumbers Treats text as numeric data for the sort. xlSortNormal (default) Sorts numeric and text data separately.