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.
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.
|
||||||
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.
|
||||||
Order2 | Optional | XlSortOrder | Determines the sort order for the values specified in Key2.
|
||||||
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.
|
||||||
Header | Optional | XlYesNoGuess | Specifies whether the first row contains header information.
|
||||||
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
|
||||||
SortMethod | Optional | XlSortMethod | Specifies the sort method of Chinese
|
||||||
DataOption1 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key1; does not apply to pivot table sorting.
|
||||||
DataOption2 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key2; does not apply to pivot table sorting.
|
||||||
DataOption3 | Optional | XlSortDataOption | Specifies how to sort text in the range specified in Key3; does not apply to pivot table sorting.
|
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