VBA & Macro Problems

How to Move and Delete an Excel Row with VBA Without Overwriting Data

John Wilson Sep 27, 2026 869 views

Question details

The user needs a macro to move a row to another worksheet when a checkbox is triggered, then delete the original row without overwriting existing records in the destination sheet.

How to Move and Delete an Excel Row with VBA Without Overwriting Data

Product: Excel
Device & OS: not provided
Scenario: Using a Worksheet_Change macro linked to a checkbox to automate row relocation across worksheets.
Observed behavior: The initial macro moved the data but overwrote the first row in larger workbooks. Additionally, deleting the source row caused repeated event triggers.

Before you start

Always create a backup copy of your workbook before running or testing new VBA macros to prevent accidental data loss. Ensure your workbook is saved in a macro-enabled format (.xlsm).

Solution 1Recommended

Use Next Available Row and Disable Excel Events

Update your macro code to dynamically find the last used row on the destination sheet and temporarily disable Excel events to prevent recursive loops when deleting the original row.

When a macro alters the worksheet (like deleting a row), it can re-trigger the Worksheet_Change event, causing an infinite loop or unexpected crashes. Disabling events temporarily solves this issue. Additionally, calculating the next available row on the destination sheet ensures older records are safely preserved.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor, and double-click the specific sheet module where your Worksheet_Change event code is located.

Define the next empty row

Create a variable to dynamically find the next empty row on your destination sheet (e.g., NextRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1).

Copy the target row

Write the code to copy the entire target row from the active sheet and paste it into the NextRow on your destination sheet.

Disable events and delete the row

Insert Application.EnableEvents = False directly before the line of code that deletes the original source row.

Restore events

Immediately after the row deletion code, insert Application.EnableEvents = True to restore normal macro functionality.

Use Next Available Row and Disable Excel Events

Error Handling: Always include an error handler (e.g., On Error GoTo Cleanup) in your macro to ensure Application.EnableEvents = True executes even if the code encounters an error.

Use Macros in WPS Spreadsheet

Automate Data with VBA Macros in WPS Office

WPS Spreadsheets provides robust support for VBA macros, allowing you to seamlessly run your existing Excel VBA scripts, including complex Worksheet_Change events, to automate your data management tasks.

1. Open your macro workbook: Launch WPS Spreadsheets and open your macro-enabled workbook (.xlsm).
2. Access the Developer tab: Go to the 'Developer' tab on the top ribbon and click 'Visual Basic' or 'Macro' to open the built-in VBA Editor.
3. Edit your macro: Paste or write your event macro code in the respective Sheet module within the editor.
4. Run the automation: Save your code and interact with your spreadsheet to trigger the automated row transfer seamlessly.

Fully compatible with Microsoft Excel .xlsm files and VBA syntaxLightweight application that runs macros quickly and smoothlyFamiliar interface that requires almost zero learning curveBuilt-in developer tools for writing, editing, and debugging code

microsoft office alternative - wps office

Frequently Asked Questions

Why does deleting a row with VBA cause my Excel to freeze or crash?

Deleting a row modifies the worksheet, which triggers the Worksheet_Change event again. If the macro itself is what deletes the row, it creates an endless recursive loop. Using Application.EnableEvents = False before the deletion prevents the macro from triggering itself.

How do I dynamically find the last row with data in Excel VBA?

You can find the last used row in a specific column (like Column A) by using the code Cells(Rows.Count, "A").End(xlUp).Row. Adding + 1 to this value gives you the first blank row where new data should be pasted without overwriting anything.

Will my Excel VBA macros work in WPS Office?

Yes, WPS Office provides excellent VBA compatibility. You can open .xlsm files, run existing macros, and use the Developer tab to edit VBA scripts seamlessly, making it a great alternative for automation tasks.

Excel & Data Problems