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:

Sort data using custom Function by Bubble Sort

Sort data using custom Function by assigning number to Text

Sort data using Table AutoFilter

Sort data using Worksheet AutoFilter

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.

excel_sort

Syntax of Excel Range Sort Method

Range .Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
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.

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

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *