VBA & Macro Problems

How to Automatically Move Deleted Excel Rows to an Archive Sheet

Huma Ashraf ChHuma Ashraf Ch Oct 9, 2026 871 views

Question details

The user wants a reliable way to automatically copy or move a row of data to a separate Archive sheet right before it is deleted from the main daily log or tracker.

How to Automatically Move Deleted Excel Rows to an Archive Sheet in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Managing daily logs, trackers, or databases where deleted records need to be automatically preserved in an archive worksheet for auditing or historical purposes.
Observed behavior
Standard Excel deletion permanently removes the row. Relying on automated deletion events in VBA can be sensitive and often copies the wrong rows after the deletion shift occurs.
Before you start

Always create a backup copy of your workbook before running or testing new VBA macros, as macro actions cannot be undone using the standard Undo feature.

Solution 1Recommended

Create a Custom VBA Button to Archive and Delete Rows

Instead of relying on unstable automatic deletion triggers, use a dedicated macro assigned to a button that archives the selected row and then deletes it in one seamless action.

Excel VBA does not have a native 'BeforeDelete' event for individual rows. Trying to use the 'Worksheet_Change' event to detect a deleted row often results in bugs, as the macro triggers after the row has already disappeared and the data below it has shifted up.

The most reliable method is to write a script that explicitly performs both actions: finding the next empty row on the Archive sheet, pasting the selected row's data there, and then deleting the original row.

1
Open the VBA Editor

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

2
Insert a New Module

In the top menu, click on 'Insert' and select 'Module' to create a blank script window.

3
Write the Archiving Script

Paste a VBA script that defines the active row, copies it, locates the last empty row on your 'Archive' sheet using 'Sheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row + 1', pastes the data, and finally executes 'ActiveCell.EntireRow.Delete'.

4
Assign the Macro to a Button

Return to your main worksheet, go to the 'Developer' tab, click 'Insert', and choose a 'Button (Form Control)'. Draw the button on your sheet and assign your newly created archive macro to it.

Create a Custom VBA Button to Archive and Delete Rows
Save as Macro-Enabled Workbook: Remember to save your file as an Excel Macro-Enabled Workbook (.xlsm) so your VBA script is preserved.
WPS Spreadsheet Automation

Automate Data Archiving with WPS Spreadsheet VBA

WPS Office fully supports advanced spreadsheet functions and VBA macros, allowing you to seamlessly run and edit your automation scripts. You can easily create an archive macro in WPS Spreadsheet to manage your daily trackers securely and efficiently.

  1. 1. Open Your Workbook in WPS Office: Launch WPS Spreadsheet and open your tracker or log workbook.
  2. 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon to access advanced tools.
  3. 3. Launch the VBA Editor: Click on 'VBA Editor' to open the macro programming environment.
  4. 4. Insert and Run Your Macro: Insert a Module, paste your custom row-archiving VBA code, and assign it to a form control button for one-click archiving.
Fully compatible with Microsoft Excel .xlsm and .xlsx formatsBuilt-in VBA editor for seamless macro scripting and customizationLightweight, fast, and highly responsive spreadsheet performanceFamiliar user interface makes migrating from Microsoft Office effortless
microsoft office alternative - wps office

Frequently Asked Questions

Why is my Worksheet_Change event copying the wrong rows?

When a row is deleted, the Worksheet_Change event triggers after the deletion has occurred. Because the rows below shift up, the macro intercepts the new data occupying that row number rather than the deleted data. This is why a manual macro button is recommended over event-based triggers for deletions.

How do I find the next available empty row in my Archive sheet using VBA?

You can dynamically locate the next empty row in VBA using the command: nextRow = Sheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row + 1. This checks from the very bottom of the sheet upwards to find the last used cell, then adds 1 to get the empty row below it.

Can I undo a row deletion made by a VBA macro?

No. Running a VBA macro clears the Excel Undo stack. Any rows deleted or moved by your archiving script cannot be restored using Ctrl+Z. Always test your scripts on dummy data first.

Does WPS Office support Excel VBA macros?

Yes, WPS Office supports VBA macros. You can open .xlsm files, access the built-in VBA editor from the Developer tab, and run scripts identically to how you would in Microsoft Office.