Multi level sort on a dynamic range

[Rackette]

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

 

[Leith Ross]

Hell Rackette,

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

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]

Try this

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]

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]

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