• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 941
  • Last Modified:

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
0
AndresHernando
Asked:
AndresHernando
  • 3
1 Solution
 
philip m o'brienCommented:
Andres,
The code below will do what you want:
 
Sub fiveColumnSort()
     'put your own orders in below
     Application.ScreenUpdating = False
     With Range("Td_MainTable")
     On Error Resume Next
     
           .Sort Key1:=Range("c_SortCrit5").Value, _
                 Order1:=xlAscending, _
                 Header:=xlYes, _
                 MatchCase:=False, _
                 Orientation:=xlTopToBottom
                 
           .Sort Key1:=Range("c_SortCrit4").Value, _
                 Order1:=xlAscending, _
                 Header:=xlYes, _
                 MatchCase:=False, _
                 Orientation:=xlTopToBottom
                 
           .Sort Key1:=Range("c_SortCrit3").Value, _
                 Order1:=xlAscending, _
                 Header:=xlYes, _
                 MatchCase:=False, _
                 Orientation:=xlTopToBottom
                 
           .Sort Key1:=Range("c_SortCrit2").Value, _
                 Order1:=xlAscending, _
                 Header:=xlYes, _
                 MatchCase:=False, _
                 Orientation:=xlTopToBottom
                 
           .Sort Key1:=Range("c_SortCrit1").Value, _
                 Order1:=xlAscending, _
                 Header:=xlYes, _
                 MatchCase:=False, _
                 Orientation:=xlTopToBottom
                 
     End With
     Application.ScreenUpdating = True
End Sub

Open in new window


Regards
0
 
AndresHernandoAuthor Commented:
Worked great!!
0
 
philip m o'brienCommented:
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,
0
 
philip m o'brienCommented:
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
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now