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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
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
 

brobles53

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
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]
 

brobles53

New Member
Joined
Jun 23, 2017
Messages
8
Hi Rick Rothstein:

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
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:

brobles53

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,924
Office Version
2010
Platform
Windows
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:

brobles53

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

Watch MrExcel Video

Forum statistics

Threads
1,099,369
Messages
5,468,223
Members
406,574
Latest member
HeinrichPaul

This Week's Hot Topics

Top