VBA & Macro Problems

Insert a Blank Row Between Different Years in Excel

Phi Hung VoPhi Hung Vo Nov 14, 2026 868 views

Question details

Insert a blank row whenever the year in column F changes, without inserting one between the header and the first data row.

Insert a Blank Row Between Different Years in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and choose Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Add the macro

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

3
Adjust the first data row

In the module, change firstDataRow = 2 if your data starts on a different row. Leave it at 2 when row 1 is the header.

4
Run the macro

Return to the worksheet you want to change, then choose Developer > Macros, select InsertRowsBetweenYears, and click Run.

5
Save the workbook

Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro in the workbook.

Run a VBA macro that compares years from the bottom up
Year values and existing blank rows: The macro compares the values in column F as they appear; it does not extract years from full dates. Existing blank cells can also create boundaries, so review column F before running it.
Work with year groups in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 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. 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. 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.
Separate adjacent year groups with blank rowsOpen and save Microsoft Excel XLSX filesUse familiar row-selection and insertion controlsEdit spreadsheets for free
microsoft office alternative - wps office

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.