Sorting Data


Range.Sort Method

Sorts a cell range, pivottable report or the current region if the specified range contains only one cell.
If the Range only contains a single cell then the currentregion is automatically used.

Range("A1:C10").Sort 
Worksheets("Sheet1").Range("A1:C10").Sort
Selection.Sort

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


Selection.Sort Key1:=Range("A3"), Order1:=xlSortOrder.xlDescending, _ 
               Key2:=Range("B3"), Order2:=xlSortOrder.xlAscending, _
               Key3:=Range("C3"), Order3:=xlSortOrder.xlDescending, _
               Header:=xlYesNoGuess.xlGuess, _
               OrderCustom:=1, _
               MatchCase:=False, _
               Orientation:=xlSortOrientation.xlSortRows, _
               DataOption1:=xlSortDataOption.xlSortNormal, _
               DataOption2:=xlSortDataOption.xlSortTextAsNumbers, _
               DataOption3:=xlSortDataOption.xlSortNormal

Key - Specifies the sort field, either as a range name (string) or range object.
Order - Determines the sort order for the values
Header - Specifies whether the first row contains headers.
MatchCase - True to do a case-sensitive sort. False to do a sort that is not case sensitive.
DataOption1 - Specifies how to sort text in Key1. It is necessary to specify xlSortTextAsNumbers when you are using ASCII sorting.



Worksheet Saved Values

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.
It is good practice to always explicitly specify all the arguments each time you use the Sort method.



Text Strings

Text strings which are not convertible to numeric data are sorted normally.
Numbers formatted as text ?



Advanced Sorting

Always use the dash "-" as your separator, do not use the underscore as the underscore appears after the numbers/characters when in ASCII order.



Different Worksheet

A different worksheet or a hidden worksheet

Sheet1.Range("A1:B20").Sort Key1:=Sheet1.Range("A2"), Order1:=xlAscending 
Sheets("Sheet1").Range(oRange.Address).Sort Key1:=Sheets("Sheet1").Range("A2"),



© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext