How to Delete Excel Rows from Comma-Separated Numbers with VBA
Question details
The user wants to create a VBA macro that reads comma-separated row numbers from a specific cell and deletes those corresponding rows in another sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the deletion of multiple specific rows based on user input provided as a comma-separated string in a single cell.
- Observed behavior
- The user needs a working VBA script. If implemented incorrectly with wrong sheet names, it triggers 'Runtime error 9: Subscript out of Range'.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and verify the exact names of the worksheet tabs you intend to reference in the VBA code to prevent runtime errors.
Use a VBA Macro to Delete Rows from the Bottom Up
Execute a VBA script that splits the comma-separated string into an array and deletes the rows in reverse order to prevent shifting issues.
When deleting multiple rows using VBA, it is crucial to delete them from the bottom upward. If you delete from the top down, the row numbers shift, causing the macro to delete the wrong rows.
Additionally, ensure the worksheet names in the code exactly match the tab names in your workbook to avoid 'Runtime error 9'.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the top menu, click 'Insert' and then select 'Module' to create a blank module window.
Copy and paste the following code into the module window: Sub DeleteMyRows() Dim s As String Dim RowArr() As String Dim i As Long s = Sheets("Data Entry").Range("I7").Value RowArr = Split(s, ",") Application.ScreenUpdating = False For i = UBound(RowArr) To LBound(RowArr) Step -1 Sheets("Records").Range("A" & RowArr(i)).EntireRow.Delete Next i Application.ScreenUpdating = True MsgBox "Job done" End Sub
Modify 'Data Entry' and 'Records' in the pasted code to match the exact worksheet tab names used in your specific workbook.
Press F5 or click the green 'Run' button in the toolbar to execute the macro and delete the specified rows.
Run VBA Macros Seamlessly in WPS Office
WPS Office provides robust support for VBA macros, allowing you to automate repetitive tasks like deleting rows based on cell values just as you would in Microsoft Excel.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
- 2. Access Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to access the macro features.
- 3. Run the Macro: Click 'Macros', select your 'DeleteMyRows' macro from the list, and click 'Run' to execute the row deletion.
Frequently Asked Questions
Why do I get 'Runtime error 9: Subscript out of Range' when running the macro?
This error occurs when the VBA code references a worksheet name that does not exist in the workbook. Check the code for Sheets("SheetName") and ensure it exactly matches the tab name at the bottom of your Excel window, including spaces and capitalization.
Why must the macro delete rows from the bottom upward?
If you delete rows from top to bottom, the rows below shift up, changing their row numbers mid-execution. Deleting from the bottom upward (using 'Step -1' in the loop) ensures that the row numbers of the remaining rows to be deleted are not affected by previous deletions.
How do I format the cell containing the row numbers?
The target cell (e.g., I7) should contain numerical values separated by commas without any trailing commas, such as '5,10,15,22'. The VBA 'Split' function reads this text string and separates it into individual row numbers to process.
