VBA & Macro Problems

Fix VBA Macro: Insert a Row When Adjacent Cell Values Do Not Match

Camila MilosovichCamila Milosovich Oct 19, 2026 868 views

Question details

The user needs to fix a VBA macro that stops iterating after inserting a blank row when adjacent cell values differ, and wants to prevent confirmation prompts when merging cells.

Fix VBA Macro: Insert a Row When Adjacent Cell Values Do Not Match
Product
Spreadsheet VBA
Device & OS
not provided
Scenario
Running a VBA macro to insert rows between mismatched adjacent cell values and merging specific cells.
Observed behavior
The loop terminates prematurely upon encountering the newly inserted blank row, and the software prompts for confirmation during cell merging.
Before you start

Before modifying your VBA code, ensure you save a copy of your workbook as a macro-enabled file (.xlsm) and back up your original data to prevent accidental data loss during testing.

Solution 1Recommended

Adjust Loop Counter and Disable Application Alerts

Modify the VBA loop to correctly skip newly inserted blank rows and disable screen alerts for a smoother, prompt-free execution.

When a macro iterates downwards and inserts a row, the next iteration will check the newly created blank row. If your macro stops at empty cells, it will terminate prematurely. To fix this, you must increment the row counter an extra time after insertion or loop backwards from the bottom up.

Additionally, disabling screen updating and display alerts will speed up the macro and prevent popup prompts when merging cells.

1
Disable Screen Updating and Alerts

At the beginning of your VBA code, add `Application.ScreenUpdating = False` and `Application.DisplayAlerts = False` to prevent screen flickering and bypass merge confirmation prompts.

2
Adjust the Loop Counter

If looping top-to-bottom, manually add `i = i + 1` (assuming `i` is your row variable) right after the code that inserts the row. Alternatively, change your For loop to iterate backwards: `For i = LastRow To 2 Step -1`.

3
Merge Cells Without Selecting

Instead of using `Select` and `Selection.Merge`, apply the merge method directly to the target range to avoid interface lag. For example, use: `Range("A" & i & ":B" & i).Merge`.

4
Restore Application Settings

Before the `End Sub` statement, ensure you turn the settings back on by adding `Application.ScreenUpdating = True` and `Application.DisplayAlerts = True`.

Adjust Loop Counter and Disable Application Alerts
Best Practice: Looping backwards (`Step -1`) is the most reliable method when deleting or inserting rows in VBA, as it prevents the row indices from shifting for the remaining unprocessed rows.
Advanced VBA Support

Run VBA and Macros Seamlessly with WPS Office

WPS Spreadsheet offers robust support for VBA macros, allowing you to automate repetitive tasks like row insertions, cell formatting, and conditional logic just like in Microsoft Excel.

  1. 1. Enable Developer Tools: Open WPS Spreadsheet, go to the 'Options' menu, and enable the Developer tab to access macro settings.
  2. 2. Open the VBA Editor: Click on the 'Developer' tab and select 'Visual Basic' or press Alt + F11 to launch the built-in VBA editor.
  3. 3. Insert Your Macro Code: Right-click on your workbook project in the Project Explorer, select 'Insert' > 'Module', and paste your adjusted VBA code.
  4. 4. Run the Macro: Close the editor, click 'Macros' in the Developer tab, select your macro from the list, and hit 'Run' to automate your task.
Fully compatible with Microsoft Excel VBA scripts and macrosBuilt-in Macro editor for writing, debugging, and executing codeLightweight application with incredibly fast script execution speeds
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA loop stop after inserting the first row?

If your loop is set to stop when it encounters an empty cell (e.g., using `Do While Cells(i, 1) <> ""`), the newly inserted blank row triggers this stop condition. You must bypass the blank row by manually incrementing your row counter or looping from the bottom upwards.

How do I merge cells in VBA without getting a warning prompt?

Spreadsheet software will warn you if you merge cells containing multiple data points. You can suppress this warning by adding `Application.DisplayAlerts = False` before your merge code, and setting it back to `True` afterwards.

Should I loop top-to-bottom or bottom-to-top when inserting rows?

It is highly recommended to loop from bottom to top (using `Step -1` in a For loop) when inserting or deleting rows. This ensures that the row numbers of the data you haven't processed yet are not pushed down or altered by the insertion.