Insert Rows and Assign Cell Values with Excel VBA
Question details
Insert a row below each nonempty source cell in column A and fill the new row with fixed values and a formula.

- 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.
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.
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.
In Excel, open the workbook, select the worksheet to modify, and press Alt+F11 to open the Visual Basic Editor.
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
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.
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.

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.

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.




