Sorting with VBA in a loop?

[Phil Smith]

Hi everyone. I have a worksheet with work shifts that take up 4 columns per day with Start, End, Shift number & Duration.
Sun in C-F sorting by E.
Mon in G-J sorting by I
Tue in K-N sorting by M
Wed in O-R sorting by Q
Thu in S-V sorting by U
Fri in W-Z sorting by Y
Sat in AA-AD sorting by AC

I am able to sort each individual group of columns by their respective columns starting from the Macro Recorder:

Sub SortShifts()
'
' 
'

'
    ActiveWorkbook.Worksheets("ROSTER").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ROSTER").Sort.SortFields.Add Key:=Range( _
        "E74:E96"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ROSTER").Sort
        .SetRange Range("C73:F96")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I tidied the code up to get this:

Sub SortShifts()
'
' Macro11 Macro
'

'
    With Worksheets("ROSTER").Sort
        
        .SortFields.Clear
        
        .SortFields.Add Key:=Range( _
        "E74:E96"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

        .SetRange Range("C73:F96")
        
        .Header = xlYes
        
        .MatchCase = False
        
        .Orientation = xlTopToBottom
        
        .SortMethod = xlPinYin
      
        
    End With
    
End Sub

I then adjusted the code for each other day and it worked as intended.

What I would like to do, if possible, is to have a loop to automatically sort each day, using the Cells(Rows.Count, 1).End(xlUp).Row method as each day has a different number of shifts and I would like to not have to hard code the bottom row in to keep it as dynamic as possible. This is where I am getting stuck.

Can anyone help please?

 

[alansidman]

Give this a try. I have not tested as I had no worksheet like yours to try on.

Option Explicit


Sub SortShifts()
Dim lr As Long, i As Long


'
    With Worksheets("ROSTER").Sort
    For i = 5 To 29 Step 4
    lr = Cells(Rows.Count, i).End(xlUp).Row
        .SortFields.Clear
        .SortFields.Add Key:=Range(Cells( _
        74, i), Cells(lr, i)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
        .SetRange Range(Cells(73, i - 2), Cells(lr, i + 1))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    Next i
    End With
    
End Sub