Excel VBA Macro to Keep Specific Rows and Delete the Rest
Question details
The user wants to run an Excel VBA macro that reads a required row count from cell M2, retains that number of data rows, and deletes all rows below the retained data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing and cleaning a dataset where only a dynamically defined number of top rows needs to be kept based on a reference cell.
- Observed behavior
- The user needs a script to automatically process the row limit from cell M2 and clear the rest of the worksheet without manual deletion.
Verify that cell M2 contains a valid numeric value greater than zero, and ensure your Developer tab is enabled in the ribbon so you can access the Visual Basic Editor.
Use a VBA Macro to Delete Excess Rows Based on a Cell Value
Create and run a VBA macro that dynamically calculates where to begin deleting rows based on the number specified in cell M2.
This macro assumes your data begins on row 2, accommodating headers in row 1. It reads the target row count from cell M2, calculates the exact starting point for deletion, and completely removes all rows down to the absolute bottom of the worksheet.
In Excel, press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.
Click on Insert in the top menu bar, and then select Module. This will open a blank window where you can write your code.
Copy and paste the following code into the module window: Sub DeleteRows() Dim rowsToKeep As Long rowsToKeep = Range("M2").Value If rowsToKeep < 1 Then MsgBox "You must leave at least one row!", vbExclamation Exit Sub End If Range(rowsToKeep + 2 & ":" & Rows.Count).Delete End Sub
Close the VBA editor. Press ALT + F8 to open the Macro dialog box, select DeleteRows, and click Run to execute the script.
Automate Data Cleaning with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for VBA macros, allowing you to run the exact same row-deleting scripts without modification. Enjoy a highly compatible, fast, and lightweight environment for your data processing tasks.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the data.
- 2. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click on Visual Basic, or simply press ALT + F11.
- 3. Run the Macro: Insert your VBA code into a module just as you would in Excel, and execute it to delete the unneeded rows instantly.
Frequently Asked Questions
Can I undo a VBA macro after it deletes the rows?
No, running a VBA macro clears the undo history in your worksheet. You cannot use CTRL + Z to restore the deleted rows. It is highly recommended to save a backup of your file or test the code on sample data before running it on important datasets.
How do I change the cell that determines the number of rows to keep?
In the provided VBA code, locate the line that says 'rowsToKeep = Range("M2").Value'. Change "M2" to your preferred cell reference, such as "A1" or "Z10", making sure to keep the quotation marks.
Why does my macro throw an error when attempting to delete rows?
This typically happens if your worksheet is protected or if you have merged cells overlapping the rows you are trying to delete. Go to the Review tab, click Unprotect Sheet, and try running the macro again.
