VBA to insert blank row in-between years

brobles53

New Member
Joined
Jun 23, 2017
Messages
8
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi brobles53,

Here's one way:

Code:
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
 
Upvote 0
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
 
Upvote 0
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...
Code:
[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]
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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...
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
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top