Grouping data to M-F workweeks.

[jaahaa]

Hi,

The formula below is working, it groups data Monday to Friday workweeks. It separates the workweek by adding 3 empty rows after the
Friday. However, if there is multiple rows with a Friday date the formula adds 3 rows after each Friday. Is there a way to add the 3 empty rows on the last Friday row? Hope this makes sense.

Sub aaaaSeparateWorkweeks()

Dim ws As Worksheet

Set ws = ActiveSheet



' Start from the last row and move upwards; this avoids rechecking rows after insertion

Dim lastRow As Long

lastRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).row



Dim i As Long

For i = lastRow To 2 Step -1 ' Assuming the first row might contain headers

If IsDate(ws.Cells(i, "L").Value) Then

' Check if the day is a Friday (Weekday function returns 6 for Friday)

If Weekday(ws.Cells(i, "L").Value) = 6 Then

ws.Rows(i + 1).Resize(3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End If

End If

Next i



Application.CutCopyMode = False

MsgBox "Workweeks separated with 3 empty rows."

End Sub

 

[LydMix]

Check if row above (i-1) is empty before adding/jumping