Insert a Blank Row Between Different Years in Excel
Question details
Insert a blank row whenever the year in column F changes, without inserting one between the header and the first data row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has names in column E and year values in column F, with a header above the data.
- Observed behavior
- The existing VBA code inserts rows between different years but also inserts an unwanted row below the header.
Check that column F contains year values and identify the first data row; the example below assumes row 1 is the header and row 2 is the first data row. Arrange the records in the desired order and fill in missing years, since the macro treats any change in adjacent values as a boundary.
Run a VBA macro that compares years from the bottom up
Use this method to insert rows automatically while excluding the header from the comparison.
The macro compares each year with the year directly above it, stopping before it compares the first data row with the header. Working upward prevents inserted rows from disrupting the remaining comparisons.
In Excel, open the workbook and choose Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.
In the VBA editor, select Insert > Module and paste the following code: Sub InsertRowsBetweenYears() Dim ws As Worksheet Dim lastRow As Long Dim firstDataRow As Long Dim i As Long Set ws = ActiveSheet firstDataRow = 2 lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row For i = lastRow To firstDataRow + 1 Step -1 If ws.Cells(i, "F").Value2 <> ws.Cells(i - 1, "F").Value2 Then ws.Rows(i).Insert End If Next i End Sub
In the module, change firstDataRow = 2 if your data starts on a different row. Leave it at 2 when row 1 is the header.
Return to the worksheet you want to change, then choose Developer > Macros, select InsertRowsBetweenYears, and click Run.
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro in the workbook.

Insert blank rows between year groups in WPS Spreadsheets
For a worksheet with a few year changes, use WPS Spreadsheets to insert blank rows manually without comparing the first data row to the header.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
- 2. Find a year boundary: In column F, start near the bottom of the data and find a row whose year differs from the row immediately above it.
- 3. Insert a blank row: Right-click the row number for the lower year group and choose Insert to add a blank row above it.
- 4. Repeat and save: Continue upward through the remaining year boundaries, without inserting a row above the first data row. Choose File > Save As to save the result.

Frequently Asked Questions
Why does the macro insert a row below my header?
The loop may be comparing the first data row with the header. Set firstDataRow to the first row containing data and stop the loop at firstDataRow + 1.
Does the macro sort the years first?
No. It inserts a row wherever adjacent values in column F differ. If you want all records for each year grouped together, sort the data by column F before running the macro.
Can I use the macro if column F contains full dates?
Not as written. The macro compares complete cell values, so different dates in the same year can trigger an insertion. Use a separate column containing extracted years or modify the comparison to use the YEAR of each date.
What happens if I run the macro twice?
Existing blank rows can be treated as value changes, so running it again may insert extra rows. Remove the previously inserted blank rows before rerunning it.




