Excel 2003 vba sort sheet in batches

[Bigger312]

Hi ,
I am at the final stage of my project and I am working on reports. I have got the report “patrol” page to this stage. I have up loaded a sample of the sheet the sheet can have up to 30 rows in each column. I have tried the macro recorder to create code to enable me to sort the page in blocks the resulting macro is very complicated, very long and unusable. What I am trying to achieve is (within the Command Button “ComPatrol “)
Worksheets("Patrol")
“A2:D33” sort column C ascending and then column D Ascending and repeat this for
“E2:G33“ sort column F ascending and then column G Ascending and repeat this for
“H2:J33” sort column i ascending and then column j Ascending and repeat this for
”K2:M33” sort column F ascending and then column G Ascending and repeat this for
”N2:P33” sort column o ascending and then column p Ascending and repeat this for
”Q2:S33” sort column r ascending and then column s Ascending and repeat this for
”T2:V33” sort column u ascending and then column V Ascending and repeat this for
The reason I am trying to sort the columns in batches is to keep the staff on each date grouped with the time and task assigned.
The result of this will be to have the shifts sorted from E 7:00 to N 22:00 and the Patrols grouped together and in the process blank cells will shift down the sheet.

I will use the same code on the other report worksheets “Enq” “Absence” and finally “Posts” in my project.
I hope that I have explained myself clearly. Thanks for you time and help.

Gerry

[HansV]

Do you really want to sort column A together with columns B:D? All other "blocks" are only 3 columns wide.

[Bigger312]

Hi Hans
Sorting Column A is unnecessary

Thanks

Gerry

[HansV]

Here you go:

Code: Select all

Sub SortBlocks()
    Dim c As Long
    For c = 2 To 20 Step 3
        Range(Cells(1, c), Cells(33, c + 2)).Sort _
            Key1:=Cells(1, c + 1), Key2:=Cells(1, c + 2), _
            Header:=xlYes
    Next c
End Sub

[Bigger312]

Thanks Again Hans

Top Class

Gerry