Using VBA to Sort Data with Excel VBA

Now let’s look at several examples related to sorting data using VBA programs.

To sort data by up to three criteria, the Sort method is applied. This method allows you to sort rows in lists, pivot tables, and databases, as well as columns in worksheets:

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

• expression — a reference to a cell range or to the range itself that will be sorted.
• Key1 — optional parameter that specifies the reference to the first field to sort.
• Order1 — optional parameter that determines the sort order for the field specified by Key1. Valid values are the following XlSortOrder constants:
  • xlAscending (ascending order),
  • xlDescending (descending order).
• Key2 — optional parameter that specifies the reference to the second field to sort.
• Type — optional parameter that specifies the elements that should be sorted. Used only with PivotTables.
• Order2 — optional parameter that determines the sort order for the field specified by Key2. Valid values are the XlSortOrder constants.
• Key3 — optional parameter that specifies the reference to the third field to sort.
• Order3 — optional parameter that determines the sort order for the field specified by Key3. Valid values are the XlSortOrder constants.
• Header — optional parameter that specifies whether the first row of the list contains headers. Valid values are the following XlYesNoGuess constants:
  • xlYes (the first row of the range contains a header, which is not sorted),
  • xlNo (the first row of the range does not contain a header, default value),
  • xlGuess (MS Excel decides whether the list has a header).
• OrderCustom — optional parameter that specifies a custom sort order. It is an integer indicating the index number of the list used as a sorting template.
• MatchCase — optional parameter that indicates whether to consider case sensitivity when sorting.
• Orientation — optional parameter that specifies the orientation of the sort. Valid values are the following XlSortOrientation constants:
  • xlTopToBottom (sorting is performed top to bottom, i.e., by rows),
  • xlLeftToRight (sorting is performed left to right, i.e., by columns).
• SortMethod — optional parameter that specifies the sorting method. Used for languages such as Chinese and Japanese.
• DataOption1 — optional parameter that specifies how text should be sorted in the field defined by Key1. Valid values are the following XlSortDataOption constants:
  • xlSortTextAsNumbers (numeric and text data are sorted together),
  • xlSortNormal (numeric and text data are sorted separately).
• DataOption2 — optional parameter that specifies how text should be sorted in the field defined by Key2. Valid values are the XlSortDataOption constants.
• DataOption3 — optional parameter that specifies how text should be sorted in the field defined by Key3. Valid values are the XlSortDataOption constants.