VBA & Macro Problems

Insert Rows and Assign Cell Values with Excel VBA

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

Insert a row below each nonempty source cell in column A and fill the new row with fixed values and a formula.

Insert Rows and Assign Cell Values with Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A worksheet has source records in column A that need an additional row containing values in columns A, B, and D and a formula in column C.
Observed behavior
The loop needs to process each qualifying source row once without repeating or skipping records after rows are inserted.
Before you start

The example assumes row 1 is a header, source records start in row 2, and every nonempty cell in column A qualifies. Test the macro on a small sample first; it inserts entire worksheet rows, and running it again will process the previously inserted rows too.

Solution 1Recommended

Loop upward and insert a populated row below each source record

Use a bottom-up loop so inserting rows does not change the positions of source rows that remain to be processed.

1
Open the VBA editor

In Excel, open the workbook, select the worksheet to modify, and press Alt+F11 to open the Visual Basic Editor.

2
Add a module

In the VBA editor, choose Insert > Module and paste this macro: Sub InsertDetailRows() Dim ws As Worksheet Dim lastRow As Long Dim r As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For r = lastRow To 2 Step -1 If ws.Cells(r, "A").Value <> "" Then ws.Rows(r + 1).Insert ws.Cells(r, "A").Offset(1, 0).Value = "SPL" ws.Cells(r, "A").Offset(1, 1).Value = "CHECK" ws.Cells(r, "A").Offset(1, 2).Formula = "=C" & r ws.Cells(r, "A").Offset(1, 3).Value = "Transfer Account" End If Next r End Sub

3
Adjust and run the macro

Change the loop's ending row from 2 if your data starts elsewhere, and adjust the values or offsets if needed. Return to the target worksheet, open Developer > Macros, select InsertDetailRows, and click Run.

4
Check the inserted rows

On the worksheet, confirm that each nonempty source cell in column A has a new row below it. Check that column C in each new row contains a formula referring to column C of the source row.

Loop upward and insert a populated row below each source record
Save the macro: To retain the VBA code, use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm).
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and migrate everyday spreadsheet work easily. VBA availability and macro compatibility can vary by WPS edition, so verify your macro before relying on it.

Insert and edit spreadsheet rows using familiar worksheet controls.Open and edit Microsoft Excel formats, including .xlsx and .xls files.Use a familiar interface for everyday spreadsheet tasks.Get a free, lightweight office suite for documents, spreadsheets, and presentations.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro loop from the bottom upward?

Inserting a row shifts rows below it. Starting at the last source row and using Step -1 leaves the unprocessed rows above in their original positions.

How do I start processing on a different row?

In the VBA editor, change the 2 in For r = lastRow To 2 Step -1 to the first data row you want to process. Rows above that number are left untouched.

What formula is written to the new row?

The statement .Formula = "=C" & r writes a reference to column C of the source row. For example, a new row inserted below row 5 receives =C5 in its column C.

Can I run the macro more than once?

Running it again will also treat previously inserted rows with SPL in column A as source records. To avoid duplicate insertions, run it once on the intended data or change the If condition to exclude rows that should not be processed.