asked on

Excel VBA - Sort a table based on criteria in input cells

What's the VBA code to sort a table based on criteria entered in named cells c_SortCrit1, c_SortCrit2... c_SortCrit5. These cells will NOT be part of the table.

The criteria will be the exact text of the desired table header cell.

These cells are not part of the table. Please see attached file with table and criteria cells.

Thanks, --Andres
SortOptions.xlsx

ASKER CERTIFIED SOLUTION

philip m o'brien

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndresHernando

ASKER

Worked great!!

philip m o'brien

Don't forget that you can add more criteria simply by adding additional case scenarios into the code.

Before doing this you need to understand how Excel does a simple three column sort...

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, for your VBA routine, they need to be in reverse order for the sorting e.g. if you wish add in a 6th criteria simply add this is as the first case scenario etc.
Regards,

philip m o'brien

Andres,
I hated the duplication in this code so I've condensed it down for you (even if you don't need it any more):

Sub fiveColumnSort()
'For more snippets see http://more.pasigraphy.co.uk/

'Turn off updating for speed
    Application.ScreenUpdating = False
'Define the table to be sorted
    With Range("Td_MainTable")
'Make sure we can handle unused criteria
    On Error Resume Next
'Reverse the sorting order and step through them
        For n = 5 To 1 Step -1
'Define the criteria for sorting
            strCriteria = "c_SortCrit" & n
                     .Sort Key1:=Range(strCriteria).Value, _
                           Order1:=xlAscending, _
                           Header:=xlYes, _
                           MatchCase:=False, _
                           Orientation:=xlTopToBottom
        Next
    End With
'Turn on updating again so that you can see the changes
    Application.ScreenUpdating = True
End Sub

Open in new window

Hopefully this will make it even easier for you to add more criteria or adapt for other situations.
Regards