How to Delete Excel Rows Based on Cell Text using VBA
Question details
The user needs to use a VBA macro to delete entire spreadsheet rows when a specific column contains certain recurring text labels.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up data by removing rows with recurring headers or total lines (e.g., 'column1' or 'total amount').
- Observed behavior
- To efficiently automate row deletion without skipping rows, which requires implementing a reverse loop in the VBA script.
Ensure you have saved a backup copy of your workbook before running the macro, as VBA actions cannot be easily undone using the standard undo button.
Use a Reverse Loop VBA Macro to Delete Rows
This is the most reliable method for deleting rows based on a condition without skipping any rows during the process.
When deleting rows using VBA, it is crucial to loop backwards (from the bottom up). If you loop top-down, deleting a row shifts subsequent rows up, causing the loop to skip evaluating the next row.
Disabling screen updating during the macro execution will also significantly speed up the data processing time and prevent screen flickering.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the top menu bar, click 'Insert' and select 'Module' to create a blank script window.
Copy and paste the following code into the module window: Sub DeleteRows() Dim iLastRow As Long Dim i As Long Application.ScreenUpdating = False iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value = "column1" Or Cells(i, "A").Value = "total amount" Then Rows(i).Delete End If Next i Application.ScreenUpdating = True End Sub
Press 'F5' or click the 'Run' button (the green play icon) in the toolbar to execute the macro. Rows matching 'column1' or 'total amount' in column A will be deleted instantly.

Use WPS Spreadsheet to Run Macros and Manage Data
WPS Office fully supports VBA macros, allowing you to run your row-deleting scripts just like you would in Microsoft Excel. Experience powerful data processing for free.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel file (.xlsm or .xlsx).
- 3. Enable the Developer Tab: Go to 'Options' > 'Customize Ribbon' and check 'Developer' to display macro tools on your ribbon.
- 4. Run Your VBA Code: Click 'Macros' under the Developer tab, paste your row deletion code into the editor, and run it seamlessly.

Frequently Asked Questions
Why does my VBA code skip rows when deleting?
If you loop from top to bottom (e.g., Row 1 to 100), deleting Row 2 moves Row 3 up to become the new Row 2. The loop then moves to Row 3 (which was originally Row 4), entirely skipping the original Row 3. Always use a reverse loop (`Step -1`) when deleting rows.
How do I delete rows based on a partial text match in VBA?
Instead of using the exact equals sign (`=`), you can use the `Like` operator with wildcards in your `If` statement. For example: `If Cells(i, "A").Value Like "*total*" Then` will delete rows containing the word 'total' anywhere in the cell.
Can I delete rows based on multiple columns?
Yes, you can modify the `If` condition to check multiple columns by using the `And` or `Or` operators. For example, `If Cells(i, "A").Value = "text" And Cells(i, "B").Value = "delete" Then`.
Why do I need to disable screen updating in the macro?
Disabling `Application.ScreenUpdating` stops the spreadsheet application from visually refreshing the screen every time a row is deleted. This vastly improves the macro's execution speed and prevents screen flickering during large operations.




