VBA to insert blank row in-between years

[brobles53]

Hello All:

I am in need for a macro that will, starting at row 9 and insert a blank row every time there is a change in year in column "C".

Any help would be extremely appreciated.

Thank you!

 

[Trebor76]

Hi brobles53,

Here's one way:

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

Regards,

Robert

 

[brobles53]

Thank you for your quick response Robert.

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?

Any thoughts?

Thank you

 

[Rick Rothstein]

I would like the code to only insert a row between a change in years only, not a change in date.

Give this macro a try...

[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]

 

[brobles53]

Hi Rick Rothstein:

The code you have me is giving me a " run time error '13' " Type mismatch.

 

[Rick Rothstein]

The code you have me is giving me a " run time error '13' " Type mismatch.

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.

 

[brobles53]

Hi Rick,

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?

 

[Rick Rothstein]

Hi Rick,

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...

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

 

[brobles53]

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"

Thank you so much!