Multi level sort on a dynamic range

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
Good afternoon...again. :)

I'm surprised I can't find some already built code on how to do a multi level sort on a dynamic data range...but I've not found any so far, so I have to bother everyone here again.

My header row is on row 7; I have 7 columns, and I need to sort first by column B (a-z) and then sort by column D (smallest to largest, 0001 to 2400).

Column B is Name (last name, first name) and column C is a 'general' format.

I used the macro recorder but, of course, that only works for a static range and I can't figure out how to take what I already have and switch it over to do this sort.

As always, any and all help/info is very, very appreciated.

-Christine
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
Hell Rackette,

This will sort your range. It assumes the first column is "A".

Code:
Sub SortRange()


    Dim cols    As Long
    Dim Rng     As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        Set RngBeg = Wks.Range("A7")
        cols = Wks.Cells(RngBeg.Row, Columns.Count).End(xlToLeft).Column - RngBeg.Column + 1
        
        Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
        
        If RngEnd.Row = RngBeg.Row Then Exit Sub
        
        Set Rng = Wks.Range(RngBeg, RngEnd).Resize(ColumnSize:=cols)
        
        With Wks.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Rng.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=Rng.Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SetRange Rng
            .Apply
        End With
        
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,703
Office Version
2007
Platform
Windows
Try this

Code:
Sub Multi_sort()
    Range("A7:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=Range("B7"), _
        order1:=xlAscending, key2:=Range("D7"), order2:=xlAscending, Header:=xlYes
End Sub
 

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
Thank you to both of you for responding. :)
Leith, yours worked but I wasn't able to respond to let you know, until now.
DanteAmor, thank you, also, for responding. I haven't yet tried your code, because I'm now worried about messing up something that took me all weekend to get working, but I will save it for use later.

-Christine
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,703
Office Version
2007
Platform
Windows
Thank you to both of you for responding. :)
Leith, yours worked but I wasn't able to respond to let you know, until now.
DanteAmor, thank you, also, for responding. I haven't yet tried your code, because I'm now worried about messing up something that took me all weekend to get working, but I will save it for use later.

-Christine
No problem, take your time. Notify me if you have any questions
 

Watch MrExcel Video

Forum statistics

Threads
1,099,006
Messages
5,465,965
Members
406,457
Latest member
Pinky Rose Jordan

This Week's Hot Topics

Top