Multi level sort on a dynamic range
Results 1 to 5 of 5

Thread: Multi level sort on a dynamic range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multi level sort on a dynamic range

    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

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,775
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level sort on a dynamic range

    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
    Sincerely,
    Leith Ross

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,279
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Multi level sort on a dynamic range

    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
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi level sort on a dynamic range

    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

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,279
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Multi level sort on a dynamic range

    Quote Originally Posted by Rackette View Post
    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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •