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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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
12,095
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
12,095
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,101,775
Messages
5,482,858
Members
407,366
Latest member
Rofsal

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top