Excel was designed so that a user can sort by 3 columns, and that’s it. Why 3? Who knows, maybe the developers figured that 3 would be enough for any user of Excel and if they wanted more they’d be using a different application entirely. For whatever reason, that’s what we’re stuck with when using the Data>Sort tool provided.
Perhaps not surprisingly, then, the question of how to sort using more than 3 columns is frequently raised on Excel forums, with most of the answers revolving around doing it manually i.e. sorting by the 3 least important columns and then repeating with the remainder.
But how to do it using code? By happy coincidence, I was asked exactly that: how to sort a specific table using a required sorting order set using drop-down selections in pre-defined cells.
Here’s the original question:
What’s the VBA code to sort a table based on criteria entered in the named cells cSortCrit1…cSortCrit5? The criteria will be the exact text of the desired table header cell. These cells are not part of the table. The data table is named tblMainTable.
And my answer:
The key is to understand how Excel does a simple three column sort. Simply put, when you stipulate that you wish to sort your data field by column A, then by column B, then by column C, Excel actually reverses this order and sorts your entire data field by column C, then by column B, and finally, by column A.
So sorting by more than 3 fields, if done manually, would simply require you to work out the reverse order and get on with it. As an example let’s say I want to sort by odd column numbers from 3 to 21 i.e. 3, 5, 7, 9, 11, 13, 15, 17, 19, 21.All I have to do is sort the whole table by column 21, then by column19, 17, 15, 13, 11, 9, 7, 5 and finally by column 3. The results will now be as expected.
Returning to the specific question of how to sort those 5 criteria: it just remains to create a routine to perform the sort function, not forgetting to turn off screen updating and to add a simple error catcher, just in case one or more of the criteria is empty (note that I’ve gone with “On Error Resume Next” in this instance but really this is just laziness coupled with the simplicity of the routine – if you have a complex routine then DO NOT do this, instead write a decent error catcher):
Sub fiveColumnSort() Application.ScreenUpdating = False With Range("Td_MainTable") On Error Resume Next For n = 5 To 1 Step -1 strCriteria = "c_SortCrit" & n .Sort Key1:=Range(strCriteria).Value, _ Order1:=xlAscending, _ Header:=xlYes, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next End With Application.ScreenUpdating = True End Sub
And that’s it. To add more criteria, just increase the step count (and ensure that any new criteria have been created, of course). Or, assuming we keep our criteria in row 1, let’s just get Excel to figure out how many criteria there are for us:
Sub multiColumnSort() Application.ScreenUpdating = False With Range("Td_MainTable") On Error Resume Next For n = Range("IV1").End(xlToLeft).Column To 1 Step -1 strCriteria = "c_SortCrit" & n .Sort Key1:=Range(strCriteria).Value, _ Order1:=xlAscending, _ Header:=xlYes, _ MatchCase:=False, _ Orientation:=xlTopToBottom Next End With Application.ScreenUpdating = True End Sub
There are ways to make it even smarter, though, such as:
- set the table name using a cell value so that code isn’t needed for every table;
- set the possible columns from this using offset and dynamic drop-downs;
- get it to count how many criteria there are and use this instead of a fixed step down (as above);
- get it to go and make a cup of tea for while we’re waiting for it to run…
…okay maybe not the last one, but hopefully you get the idea of what’s possible.