Need help modifying a macro to insert rows after date change.

try this way:

Sub Deli()
    Dim LastRow As Long, i As Long
    LastRow = Cells(Rows.Count, 13).End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Range("M" & i) = Date Then
            Range("M" & i).Rows.EntireRow.Insert shift:=xlDown
            Range("M" & i).EntireRow.Interior.Color = 5296274
        End If
    Next
End Sub

and let me know...

Excellent! And thanks for the quick response.

So the code you posted does the initial 'inserting line before todays date'.

So can I then add the code in my original post after that to go through the rest of the sheets after todays date and add a line after each date change?

eg.....

Sub Deli()
    Dim LastRow As Long, i As Long
    LastRow = Cells(Rows.Count, 13).End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Range("M" & i) = Date Then
            Range("M" & i).Rows.EntireRow.Insert shift:=xlDown
            Range("M" & i).EntireRow.Interior.Color = 5296274
        End If
    Next
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
For i = LastRow To 2 Step -1
    If Int(Range("M" & i).Value) <> Int(Range("D" & i - 1).Value) Then
        Range("M" & i).Rows.EntireRow.Insert shift:=xlDown
    End If
Next i

End Sub

Does the second part of the code need modifying to start after todays date? Much appreciated :-)

edit: It doesn't like having two Dim as Long. What can I change the second one to?

Can you attach the workbook so i can see the structure of the dates? Please hide any sensible confidential data.

Should hopefully be attached. I have removed some of the rows with sesitive data, so column 'M' (which did have the dates) is now column G.

I have also added the colour lines so you can see what I would like the end result to look like.

So there is normally between 0-10 rows before todays date. Then after that there could be any number of date groupings, as you should be able to see.

EXAMPLE.xlsx

So

Sub Deli()
    
    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, 13).End(xlUp).Row
    Do
        If Range("M" & LastRow - 1) <> "" And Range("M" & LastRow) <> "" Then
            If Range("M" & LastRow - 1) <> Range("M" & LastRow) Then
                Range("M" & LastRow).Rows.EntireRow.Insert shift:=xlDown
                Range("M" & LastRow).EntireRow.Interior.Color = 9868950
            End If
        End If
        LastRow = LastRow - 1
        
    Loop While Range("G" & LastRow) = "" Or Range("M" & LastRow) >= Date
    
End Sub

Put this in a module and run it on the worksheet you need.

AWESOME! It works!

So I just needed to combine the IF statements.

Thank you very much for your help. Hopefully in a year or two I will know enough to help people like you do!

Glad to help. Mark SOLVED and Add Reputation if my answer pleases you.

Hello again bulina2K,

I wonder if you might be able to help make a slight modification to this. We have been a little too efficient here, and managed to get all the backlog product out of the door.

This means, that the first part of the macro that inserts a line before "todays date" comes back as a run time error, because there is nothing before "todays" date.

Is there a modification that can be added to the 'If' statement to ignore it if there is nothing before "todays date"?

Thanks again.

add this line of code first thing in Do loop

If LastRow = 1 Then Exit Do

so:

Sub Deli()
    
    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, 13).End(xlUp).Row
    Do
        If LastRow = 1 Then Exit Do   ''' if the first row contains today stop looping and exit.
        If Range("M" & LastRow - 1) <> "" And Range("M" & LastRow) <> "" Then
            If Range("M" & LastRow - 1) <> Range("M" & LastRow) Then
                Range("M" & LastRow).Rows.EntireRow.Insert shift:=xlDown
                Range("M" & LastRow).EntireRow.Interior.Color = 9868950
            End If
        End If
        LastRow = LastRow - 1
        
    Loop While Range("M" & LastRow) = "" Or Range("M" & LastRow) >= Date
    
End Sub

let me know..

Sweet, that has fixed it. Many thanks once again. Wish I knew it was that simple!

Ignore, I'll start a new thread.