Databases Reference
In-Depth Information
FIGURE 1.11
Additional sorting options available in Excel.
In code, the sorting columns are referred to as Keys, and the first Key has the highest precedence.
Only one Key (or column) is required to sort data. However, if there is a chance that elements in
the column could be of equal value, it is often essential to have a second or even third Key (or column)
to sort upon when elements in the preceding Key of precedence turn up equal. In addition, each
Key can be sorted in either ascending or descending order. If a header exists in the Worksheet, this
information can be specified, and the header rows at the top of the Worksheet will not be sorted.
The area of the Worksheet to be sorted must be specified as a range (usually of columns), which
is selected prior to specifying the Keys and sorting.
The variety of parameters that can be set when sorting a Worksheet proves problematic when
trying to create a generic subroutine. Which parameters should be included and which should be
excluded? The following subroutine is a good compromise and lends itself to use in a variety of
situations. Figure 1.11 shows additional options available when sorting data in Excel.
Sub SortWorksheet(ByVal wkbook, ByVal wksht, ByVal startcol
As Integer _
, ByVal endcol As Integer, ByVal Header As Integer, ByVal ColKey1, _
ByVal Key1SortAscending As Boolean, ByVal ColKey2, _
ByVal Key2SortAscending As Boolean)
'Sorts the Columns Specified in a particular Worksheet by Keys
'Use Up to 2 Keys, for only one Key set ColKey2 = "" (Null $)
'Header = # of Header Rows: 0 = No Header
Dim Key1Range$, Key2Range$, Key1Dir$, Key2Dir$, Hdr%
'Select Workbook and Worksheet
ActivateWorkbook (wkbook)
Worksheets(wksht).Select
'Select Columns to Sort
Columns(GetColRng(startcol, endcol)).Select
'Set
Header Range
Select Case Header
Case 0
'Worksheet is devoid of Header Row
Hdr = xlNo
If IsNumeric(ColKey1) = False Then
'A Letter was passed as the Col #
Key1Range$ =
Trim(ColKey1)
& "1"
