VBA & Macro Problems

Excel VBA Macro to Delete Rows Up to a Matching Text Value

Jason Miller Oct 21, 2026 868 views

Question details

The user needs a way to locate a specific exact text string within a column and programmatically delete every row from row 1 down to the row containing that target text.

How to Use Excel VBA to Delete Rows Through a Matching Text Value

Product: Excel
Device & OS: not provided
Scenario: Cleaning up raw data reports where irrelevant header information, empty spaces, or system-generated rows precede the actual data needed.
Observed behavior: The desired outcome is a VBA script that searches column A for 'Sales Credential', removes rows 1 through the matched row, and provides a warning prompt if the text is entirely absent.

Before you start

Save your document as an Excel Macro-Enabled Workbook (.xlsm) and ensure that macros are enabled in your Trust Center security settings before running any code.

Solution 1Recommended

Create and Run a Row Deletion Macro

Use the built-in Visual Basic Editor to write a custom macro that automates the search and deletion process.

This solution relies on the VBA Find method to search for a specific string in your desired column. If a match is discovered, the script identifies its row number and selects the entire block of rows from the very top of the sheet down to the matched row, deleting them instantly. If no match is found, it prevents errors by returning a simple message box.

Open the Visual Basic Editor

In your open Excel workbook, press the Alt + F11 keys on your keyboard simultaneously to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu bar, then select 'Module' from the dropdown list to open a blank code window.

Paste the Macro Code

Copy and paste the following code into the empty module window: Sub DeleteSomeRows() Dim rng As Range Set rng = Range("A:A").Find(What:="Sales Credential", LookAt:=xlWhole) If rng Is Nothing Then MsgBox "'Sales Credential' was not found in column A!", vbExclamation Else Range("A1:A" & rng.Row).EntireRow.Delete End If End Sub

Run the Macro

Close the VBA Editor to return to your worksheet. Press Alt + F8 to open the Macro dialog box, select 'DeleteSomeRows' from the list, and click the 'Run' button.

Customizing the Search Target: To find a different text string, replace 'Sales Credential' in the code with your specific target word. Change 'A:A' if your target text is located in a different column.

Advanced VBA Support in WPS

Run Complex Macros Seamlessly in WPS Spreadsheet

WPS Office offers robust native support for Visual Basic for Applications (VBA), allowing you to write, edit, and execute complex macros just like in Microsoft Excel. Automating repetitive tasks like conditional row deletion takes only seconds.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm data file.
2. Access the Developer Tab: Click on the 'Developer' tab located on the top ribbon menu to view macro options.
3. Launch the VBA Editor: Click the 'Visual Basic' icon or press Alt + F11 to open the integrated code editor.
4. Execute Your Script: Insert a module via the Insert menu, paste your row deletion code, and click 'Run' to automate your data cleanup.

Native compatibility with Microsoft Excel macro formats (.xlsm)Familiar VBA Editor interface with full debugging toolsFast and lightweight data processing for massive datasetsFree Office suite alternative with an intuitive user experience

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro say the text was not found when it visibly exists in the column?

The macro looks for an exact cell match because of the 'LookAt:=xlWhole' parameter. Ensure there are no leading or trailing spaces in your cell. If you want to find partial text matches within a cell, change the parameter in the code to 'LookAt:=xlPart'.

Can I undo the row deletion after the macro successfully runs?

No, actions executed via a VBA macro bypass the standard Excel undo stack and cannot be reversed using Ctrl+Z. It is highly recommended to save a backup copy of your dataset before running any structural macros.

How do I change the code to target column B instead of column A?

To search column B, modify the range object in the code from Range("A:A") to Range("B:B"). You should also update the deletion range identifier from "A1:A" to "A1:B" so that it creates a valid range reference before calling the EntireRow.Delete command.

Excel & Data Problems