Fix VBA Macro: Insert a Row When Adjacent Cell Values Do Not Match
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.

- 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 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.
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.
At the beginning of your VBA code, add `Application.ScreenUpdating = False` and `Application.DisplayAlerts = False` to prevent screen flickering and bypass merge confirmation prompts.
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`.
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`.
Before the `End Sub` statement, ensure you turn the settings back on by adding `Application.ScreenUpdating = True` and `Application.DisplayAlerts = True`.

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. Enable Developer Tools: Open WPS Spreadsheet, go to the 'Options' menu, and enable the Developer tab to access macro settings.
- 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. Insert Your Macro Code: Right-click on your workbook project in the Project Explorer, select 'Insert' > 'Module', and paste your adjusted VBA code.
- 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.

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.




