Option Explicit
Sub Macro1()
Dim lngMyRow As Long
Application.ScreenUpdating = False
'Best to work backwards through the rows when inserting (or deleting) a row at a time like here.
For lngMyRow = Cells(Rows.Count, "C").End(xlUp).Row To 9 Step -1
If Range("C" & lngMyRow) <> Range("C" & lngMyRow - 1) Then
Rows(lngMyRow).EntireRow.Insert
End If
Next lngMyRow
Application.ScreenUpdating = True
End Sub
I would like the code to only insert a row between a change in years only, not a change in date. I have seem the change in date code, but I have not found an easy way to modify it to only insert a row when the year has changed?
[table="width: 500"]
[tr]
[td]Sub SeparateYearsByBlankRow()
Dim R As Long
For R = Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
If Year(Cells(R, "C").Value) <> Year(Cells(R - 1, "C").Value) Then Rows(R).Insert
Next
End Sub[/td]
[/tr]
[/table]
Do your cells in Column C actually contain real Excel dates or are they only text that happen to look like dates? You can test if a cell contains a real date using the ISNUMBER function; for example,
=ISNUMBER(C2)
If that returns TRUE, then C2 contains a real date.
The final row of my data set is not a date, it seems like the last row is what is throwing off the code you created. The last row in column "C" will always be empty. Do you know how to overcome this?
The final row of my data set is not a date, it seems like the last row is what is throwing off the code you created. The last row in column "C" will always be empty. Do you know how to overcome this?
We just need to start the loop one row higher up than the last data cell then...
Code:
Sub SeparateYearsByBlankRow()
Dim R As Long
For R = Cells(Rows.Count, "C").End(xlUp).Row [B][COLOR="#FF0000"]- 1[/COLOR][/B] To 2 Step -1
If Year(Cells(R, "C").Value) <> Year(Cells(R - 1, "C").Value) Then Rows(R).Insert
Next
End Sub
That worked great! Would you also know how to insert a row that sums times in column "B" in a new row above the one inserted? Effectivly I want two rows to be inserted, The Top row to show total time from column "B", and the lower row to say "annual training Completed"