Sorting with criteria

Mike2

New Member
Joined
Jan 5, 2019
Messages
41
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Assuming the header are at row 1, try this:

Code:
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
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback.:)
 
Upvote 0
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?
 
Upvote 0
You got it wrong here:

Code:
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:

Code:
[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:
Code:
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
Code:
Dim ra As Range, rb As Range, rc As Range, rd As Range
 
Upvote 0
And here's another way to do it:

Code:
[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]
 
Upvote 0
Ah i see...great, the code works. @Akuini : Thank you so so much for your help !!

Your 2nd coding suggestion seems easier to follow...
 
Last edited:
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top