Excel macro to sort multiple dynamic ranges

Question

I'm looking for a macro to sort multiple dynamic ranges within the same sheet. Each range is separated from the next by an empty cell in column B. Ideally, I would like the code to go through column B, find the first dynamic range, select/activate all rows (.EntireRow) in that range, sort ascending based on column C, and then go down to the next dynamic range and so on. Is this possible?

Well yes. But each range should have a header otherwise the first cell will not move during sorting. — L42
Thanks for the reply @L42, it's good to know it's possible. Each range has a header in column C (next to the blank B cell) but each header is specific to that range so there's 400+ headers. Any ideas? — Raymond
Record a macro while sorting the first few blocks, then try editing the resulting code. Post back (with code) if you run into problems, and describe exactly what the problem is. — Tim Williams
Thank you very much @Jeeped, your code does EXACTLY what I wanted to do. Btw, this code went through about 950 rows in about 15-20 sec which is great as far as I'm concerned. I have a question though, could you please briefly explain this part of your code? — Raymond

Unknown Unknown · Accepted Answer · 2015-01-14T07:51:18

If it can be assumed that a blank cell in column B indicates a new section to be sorted and that there will always be at least one row of data to sort, the following should accomplish your goal.

Sub sort_sections()
    Dim fr As Long, rws As Long, lc As Long

    With ActiveSheet
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        For fr = 1 To .Cells(Rows.Count, 3).End(xlUp).Row
            If IsEmpty(.Cells(fr, 2)) Then
                rws = .Evaluate("SMALL(INDEX(ROW(B:B)+SIGN(LEN(B:B))*1E+99, , )" & _
                                  ", COUNTBLANK(B$1:B" & fr & ")+1)-1") - (fr - 1)
                With .Cells(fr, 1).Resize(rws, lc)
                    Debug.Print .Address(0, 0)  'output the section address to the Immediate window
                    .Cells.Sort Key1:=.Columns(3), Order1:=xlAscending, _
                        Orientation:=xlTopToBottom, Header:=xlYes
                End With
            End If
        Next fr
    End With

End Sub

That will turn this:

Section Sorting

... into this,

Section Sorting

There are a number of ways to collect the extent of the section by looking for the next blank cell in column B. With no sample data provided, I chose a rather complex method that should account for variations in data but it is calculation heavy. Four hundred sections will take a few minutes.

Excel macro to sort multiple dynamic ranges

1 Answers