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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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,207
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,207
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,102,645
Messages
5,488,077
Members
407,623
Latest member
Deigs

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top