VBA & Macro Problems

How to Use VBA to Insert Rows When a Column Value Changes

Jason Miller Oct 20, 2026 868 views

Question details

The user needs a VBA macro to detect changes in adjacent values within a specific column and insert a new row automatically at each change point while copying a predefined range into the new rows.

How to Use VBA to Insert Rows When a Column Value Changes

Product: Spreadsheet
Device & OS: not provided
Scenario: Automating data organization by separating grouped data with newly inserted rows and retaining specific headers or summary formulas.
Observed behavior: The user wants to evaluate column D, insert a blank row upon detecting a value change, and paste the range A2:O2 into the newly created row while keeping the original formatting and formulas.

Before you start

Ensure you save a backup of your workbook before running the macro, and verify that the Developer tab is enabled in your ribbon to access the VBA Editor.

Solution 1Recommended

Use a Bottom-Up VBA Loop to Insert Rows and Copy Data

Running the loop from the bottom upward ensures that inserting new rows does not offset the row counter during the macro execution.

When inserting or deleting rows in VBA, always loop backwards through the rows. If you loop forwards (top to bottom), inserting a row shifts everything down, causing the script to skip rows or process them incorrectly.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu bar and select 'Module' to create a blank workspace for your macro.

Paste the Macro Code

Copy and paste the following code into the module window: Sub InsertRowsWhenColumnDChanges() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row For i = lastRow To 3 Step -1 If ws.Cells(i, "D").Value <> ws.Cells(i - 1, "D").Value Then ws.Rows(i).Insert Shift:=xlDown ws.Range("A2:O2").Copy Destination:=ws.Range("A" & i & ":O" & i) End If Next i End Sub

Run the Macro

Close the VBA editor window to return to your worksheet. Press ALT + F8, select 'InsertRowsWhenColumnDChanges' from the list, and click 'Run'.

Use a Bottom-Up VBA Loop to Insert Rows and Copy Data

Preserving Formulas and Formatting: Using the 'Destination' parameter in the copy method behaves exactly like PasteAll. It successfully retains all formatting, data, and formulas from your copied range (A2:O2).

Advanced Macros in WPS Spreadsheet

Automate Data Sorting with WPS Spreadsheet

WPS Office provides robust support for VBA and macros in its premium and enterprise versions, allowing you to easily automate complex data formatting and row insertion tasks.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the options menu to enable the Developer tab, and click on 'Macros'.
2. Insert the Code: Click 'Visual Basic Editor', insert a new module, and paste your VBA script for inserting rows.
3. Run the Automation: Save your file as a macro-enabled workbook (.xlsm) and run the macro to instantly format your dataset.

Extensive support for VBA macros and scriptingSeamless compatibility with Microsoft Excel (.xlsx, .xlsm)Lightweight architecture that runs smoothly on all devicesFree to download with an intuitive tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to loop backwards when inserting rows using VBA?

When you loop forwards (top to bottom) and insert a row, all subsequent rows shift down, altering their row numbers. This shift causes the loop to skip rows or process the same row multiple times. Looping backwards from the bottom up prevents this shift from affecting rows that haven't been evaluated yet.

How can I change the column being evaluated in the macro script?

In the provided VBA script, change the letter 'D' to your target column letter in both the `lastRow = ...` line and the `If ws.Cells(i, "D")...` line to evaluate a different column.

How do I modify the copied range in the macro?

Locate the code line `ws.Range("A2:O2").Copy`. You can simply change 'A2:O2' to any cell or range reference you wish to duplicate into the newly inserted rows.

Does WPS Spreadsheet support running Excel VBA macros?

Yes, specific versions of WPS Office (such as WPS Office Pro and Enterprise) fully support VBA. You can run, edit, and save macro-enabled workbooks (.xlsm) smoothly, just as you would in Microsoft Excel.

Excel & Data Problems