Using VBA, how do I sort a variable number of range "blocks" ?

[Johnk15]

I'm trying to figure out how to use VB to sort a variable number of range "blocks" on a worksheet. For some reason, I can't get an image file to upload here so I'll try to explain.

My worksheet has a "header block" which is range ("D1:BM9") and below the header block is a variable number of "agenda blocks", (which are identical in structure) the first of which is range ("D10:BM19"), the second would be range ("D20:BM29") and so forth, depending on how many agenda blocks the user has added. Cells D10 & D20, etc. display the row number (10, 20, etc.) which then becomes the identifying number for that agenda block.

Using a macro button, I would like to be able to sort the variable number of agenda blocks based on either the its priority which is defined as text (Critical, High, Medium, or Low) in range ("D15") for the first agenda block and range ("D25") in the second agenda block, etc., or by the person accountable which is defined as text (First & Last Name) in range ("Z12") for the first agenda block, and range ("Z22") in the second agenda block, etc.

The code below shows that I'm assuming I need to first determine the number of agenda blocks on the worksheet and then find a way to name the ranges so I can sort them but I'm not convinced this is right. And clearly, you can see where I'm stuck!

Sub SortAgendaBlocks()

' This macro sorts the agenda blocks by either priority or person accountable

' Find the number of agenda blocks on the worksheet

Dim RngNum As Integer
Dim RowStart As Long
Dim RowEnd As Long


RngNum = Application.Sum(Range("d10:d1000"))
RngNum = RngNum - 10
RngNum = RngNum / 10


' Name the ranges

RowStart = 10
RowEnd = RowStart + 9


For i = 1 To RngNum
Cells("d" & RowStart, "d" & RowEnd).Name = AB
Next i

End Sub

If anyone would provide some code or guidance, I would appreciate it very much. Thanks very much for considering this! John

 

[onlyadrafter]

Hello,

I would suggest you use dummy columns (in this case BN, BO and BP), to store data then sort by these columns, then clear these columns.

How is this for starters?

Sub SORT_BLOCK()
    For MY_ROWS = 12 To Range("Z" & Rows.Count).End(xlUp).Row Step 10
        For MY_PRIORITY = MY_ROWS - 2 To MY_ROWS + 7
            Range("BN" & MY_PRIORITY).Value = Range("D" & MY_ROWS + 3).Value
            Range("BO" & MY_PRIORITY).Value = Range("Z" & MY_ROWS).Value
            Range("BP" & MY_PRIORITY).Value = MY_PRIORITY + 3
        Next MY_PRIORITY
    Next MY_ROWS
    Range("D10:BP" & Range("BP" & Rows.Count).End(xlUp).Row).Select
    Selection.Sort Key1:=Range("BN10"), Order1:=xlAscending, Key2:=Range( _
        "BO10"), Order2:=xlAscending, Key3:=Range("BP10"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom
        Columns("BN:BP").ClearContents
End Sub

Guess the sort method needs defining to your exact needs.