Sorting with criteria

[Mike2]

Hello,

Can someone please help to suggest how to do dynamic multiple sorting with criteria in VBA?


I have a data table with header title : LOCATION, ITEM#, CATEGORY in different columns that i would like to sort the table in the following column sequence.


1st sort : LOCATION
2nd sort: CATEGORY
3rd sort : ITEM#


The Header Title is dynamically in different column on each report when the data table was extracted from the database.


Thank you for your help in advance.

 

[Akuini]

Assuming the header are at row 1, try this:

Sub a1084320a()
'https://www.mrexcel.com/forum/excel-questions/1084320-sorting-criteria.html
Dim ra As Range, rb As Range, rc As Range
Set ra = Rows(1).Find("LOCATION")
Set rb = Rows(1).Find("CATEGORY")
Set rc = Rows(1).Find("ITEM#")

Range("A1").CurrentRegion.Sort key1:=ra, order1:=xlAscending, key2:=rb, order2:=xlAscending, _
            key3:=rc, order3:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

End Sub

 

[Mike2]

Hi Akuini,

Thank you for your code suggestion. It works great !!
Thank you.

My apologies for posting two Thread for the same question.
For some reason, my first Thread Posting did not get posted onto the forum and gave me the error message
indicating the posting required approval from the forum administrator. So, i did not know it got posted after i posted the 2nd thread.
Sorry to have caused the trouble for maintenance.

 

[Akuini]

You're welcome & thanks for the feedback.

 

[Mike2]

What if i would like to do a fourth level sorting to my table?

Adding Serial# column to be sorted at the fourth level sorting order.

Dim ra, rb, rc, rd As Range

set ra=rows(1).find("Location")
set rb=rows(1).find("Category")
set rc=rows(1).find("Item#")
set rd=rows(1).find("Serial#")

range("A1").currentregion.sort Key1:=rd, order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

range("A1").currentregion.sort Key1:=ra, order2:=xlAscending, Key2:=rb, order3:=xlAscending, Key3:=rc, order4:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

I have encountered a compiler error for Invalid use of property with Key:1=rd highlighted.
Thought i was supposed to do the last level sort first in backward order?
What went wrong and how do i overcome this challenge?

 

[Akuini]

You got it wrong here:

range("A1").currentregion.sort Key1:=ra, [COLOR=#b22222]order2[/COLOR]:=xlAscending, Key2:=rb, [COLOR=#b22222]order3[/COLOR]:=xlAscending, Key3:=rc, [COLOR=#b22222]order4[/COLOR]:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

it should be:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1084320c()                                                                                                                       
[I][COLOR=seagreen]'Dim ra, rb, rc, rd As Range wrong way to declare[/COLOR][/I]                                                                                                          
[COLOR=Royalblue]Dim[/COLOR] ra [COLOR=Royalblue]As[/COLOR] Range, rb [COLOR=Royalblue]As[/COLOR] Range, rc [COLOR=Royalblue]As[/COLOR] Range, rd [COLOR=Royalblue]As[/COLOR] Range       
                                                                                                                                                                                          
[COLOR=Royalblue]Set[/COLOR] ra = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"Location"[/COLOR])                                                                                    
[COLOR=Royalblue]Set[/COLOR] rb = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"Category"[/COLOR])                                                                                    
[COLOR=Royalblue]Set[/COLOR] rc = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"Item#"[/COLOR])                                                                                       
[COLOR=Royalblue]Set[/COLOR] rd = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"Serial#"[/COLOR])                                                                                     
                                                                                                                                                                                          
Range([COLOR=brown]"A1"[/COLOR]).CurrentRegion.Sort Key1:=rd, order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom                                                              
                                                                                                                                                                                          
Range([COLOR=brown]"A1"[/COLOR]).CurrentRegion.Sort Key1:=ra, order1:=xlAscending, Key2:=rb, order2:=xlAscending, Key3:=rc, order3:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom
                                                                                                                                                                                          
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

note:
if you declare like this:

Dim ra, rb, rc, rd As Range

then ra, rb, rc will be variant type not Range.
You need to declare each variable as Range

Dim ra As Range, rb As Range, rc As Range, rd As Range

 

[Akuini]

And here's another way to do it:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1084320b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1084320-sorting-criteria.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] ra [COLOR=Royalblue]As[/COLOR] Range, rb [COLOR=Royalblue]As[/COLOR] Range, rc [COLOR=Royalblue]As[/COLOR] Range, rd [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Set[/COLOR] ra = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"LOCATION"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] rb = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"CATEGORY"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] rc = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"ITEM#"[/COLOR])
[COLOR=Royalblue]Set[/COLOR] rd = Rows([COLOR=crimson]1[/COLOR]).Find([COLOR=brown]"Serial#"[/COLOR])
 
[COLOR=Royalblue]With[/COLOR] ActiveSheet.Sort
 .SortFields.Clear
 .SortFields.Add [COLOR=Royalblue]Key[/COLOR]:=ra, [COLOR=Royalblue]Order[/COLOR]:=xlAscending
 .SortFields.Add [COLOR=Royalblue]Key[/COLOR]:=rb, [COLOR=Royalblue]Order[/COLOR]:=xlAscending
 .SortFields.Add [COLOR=Royalblue]Key[/COLOR]:=rc, [COLOR=Royalblue]Order[/COLOR]:=xlAscending
 .SortFields.Add [COLOR=Royalblue]Key[/COLOR]:=rd, [COLOR=Royalblue]Order[/COLOR]:=xlAscending
 .SetRange Range([COLOR=brown]"A1"[/COLOR]).CurrentRegion
 .Header = xlYes
 .Apply
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Mike2]

Ah i see...great, the code works. @Akuini : Thank you so so much for your help !!

Your 2nd coding suggestion seems easier to follow...

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.