Multiple Level Sorting Using VBA
In this article we build on the Sorting with VBA exercise. This is about sorting on multiple levels with VBA in the most efficient way.
The following is procedure will sort first column A in ascending order then column B in ascending order and finally column C in ascending order. The data looks as follows;
The data is described in columns A-C so the procedure needs to sort all three columns. Each will be sorted in ascending order.
The VBA Sort Procedure
The following VBA sorting procedure will sort the above data. It uses a static range for demonstration purposes and the data will be sorted in ascending order for all three columns.
The code can be easier to view wher the 1s stand for xlAscending so:
[A8:G34].Sort [a2], xlAscending, [b2], , xlAscending, [C2], xlAscending
will perform the same action as the first Excel VBA example.
Of course this is based on a static range. To make the range dynamic something like the following should do.
After the procedure has run the data looks as follows. To make the procedure more robust the following makes it dynamic. Be sure to always start a sort in the first row of data after the headings. This keeps the VBA procedure simpler.
Sub MultiSort2() 'Sort on multiple levels with VBA with a dynamic range
Dim rng As Range
Set rng=Range("A2:G" & [G1048576].End(xlUp).Row)
The attached file shows a working model using the above Excel VBA procedure.
