VBA & Macro Problems

How to Delete Excel Rows Based on Cell Text using VBA

Anum MushtaqAnum Mushtaq Nov 10, 2026 869 views

Question details

The user needs to use a VBA macro to delete entire spreadsheet rows when a specific column contains certain recurring text labels.

How to Delete Excel Rows Based on Text in a Column using VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) Editor.

2
Insert a New Module

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

3
Paste the VBA Code

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

4
Run the Macro

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 a Reverse Loop VBA Macro to Delete Rows
Customizing the Code: You can change 'A' to another column letter if your target text is located elsewhere. You should also replace 'column1' and 'total amount' with the exact text labels you want to search for.
Efficient Spreadsheet Management

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. 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel file (.xlsm or .xlsx).
  3. 3. Enable the Developer Tab: Go to 'Options' > 'Customize Ribbon' and check 'Developer' to display macro tools on your ribbon.
  4. 4. Run Your VBA Code: Click 'Macros' under the Developer tab, paste your row deletion code into the editor, and run it seamlessly.
Fully compatible with Microsoft Excel (.xlsx, .xlsm) files and VBA macros.Lightweight software that runs smoothly on older devices without lag.Advanced data processing capabilities inside a free, user-friendly interface.
microsoft office alternative - wps office

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.