How to Delete Row If Cell Contains Value Using Macro in Excel (2 Examples)

In this article, we will use a VBA macro to delete cells that contain either a string or numbers, as in the following gif.

Demonstrating a Way to Delete Row If a Cell Contains Value by Using Macros in Excel

The VBA syntax to delete rows based on cell values is:

If Then Rows(“[Row_Numbers]”).EntireRow.Delete

Here,

Row_Numbers refers to the row numbers to delete,

Delete method is applied on an object of Rows, and

EntireRow.Delete is defined as a method that will delete the entire row from the dataset.

Method 1 – Delete Row if Cell Contains String Value

Consider the following dataset of rows, containing a list of names. We want to delete the row containing the name “Ambrose”.

Steps:

Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

In the pop-up code window, from the menu bar click Insert -> Module.

Copy the following code and paste it into the code window:

Sub Delete_Row_If_Cell_Contains_String()
Dim Row As Long
Dim Wrk As Long
Row = 15
For Wrk = Row To 1 Step -1
    If Cells(Wrk, 2) = "Ambrose" Then
        Rows(Wrk).Delete
    End If
Next
End Sub

The code is now ready to run.

To run the macro: press F5 on the keyboard, or select Run -> Run Sub/UserForm from the menu bar, or click on the small Play icon in the sub-menu bar.

The row containing the name “Ambrose” is deleted from the list.

VBA Code Explanation

Sub Delete_Row_If_Cell_Contains_String() -> Starts the program and sub procedure.
Dim Row As Long -> Declares the variable Row as long data type to store the last row number.
Dim Wrk As Long -> Declares the variable Wrk as long data type to initiate in the For Loop.
- Long is a numerical data type in VBA that can hold values from 0 to 2, 147, 483, 647 for positive numbers. For negative numbers, it can hold from 0 to -2, 147, 483, 648.
Row = 15 -> Stores the last row value in the declared Row variable (there are 15 rows in our worksheet).
For Wrk = Row To 1 Step -1 -> Runs the For Loop.
If Cells(Wrk, 2) = “Ambrose” Then -> Checks each cell to see whether the value of the cell is equal to “Ambrose”.
Rows(Wrk).Delete -> If the above If Statement is true, then delete the row.
End If -> Ends the Statement.
Next -> Closes the Loop.
End Sub -> Ends the Function.

Method 2 – Insert VBA Macro to Delete Row if Cell Contains Number Value

Consider the following dataset, where some of the Names contain number values. Let’s delete all the rows containing numbers as Names.

Steps:

Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

In the pop-up code window, from the menu bar, click Insert -> Module.

Copy the following code and paste it into the code window:

Sub Delete_Row_If_Cell_Contains_Number()
Dim Row As Long
Dim Wrk As Long
Row = 15
For Wrk = Row To 5 Step -1
    If IsNumeric(Cells(Wrk, 2)) Then
        Rows(Wrk).Delete
    End If
Next
End Sub

The code is now ready to run.

To run the macro: press F5 on the keyboard, or select Run -> Run Sub/UserForm from the menu bar, or click on the small Play icon in the sub-menu bar.
From the pop-up Macros window, select Delete_Row_If_Cell_Contains_Number.
Click Run.

All the rows containing numbers as Names are deleted from the list.

VBA Code Explanations to Delete a Specific Row from Excel

Sub Delete_Row_If_Cell_Contains_Number() -> Starts the program and sub procedure to write the VBA code.
Dim Row As Long -> Declares the variable Row as long data type to store the last row number.
Dim Wrk As Long -> Declares the variable Wrk as long data type to initiate in the For Loop.
Row = 15 -> Stores the last row value in the declared Row variable (there are 15 rows in our worksheet).
For Wrk = Row To 1 Step -1 -> Runs the For Loop.
If IsNumeric(Cells(Wrk, 1)) Then -> Checks each cell to see whether the value of the cell is a number.
Rows(Wrk).Delete -> If the above If Statement is true, then delete the row.
End If -> Ends the Statement.
Next -> Closes the Loop.
End Sub -> Ends the Function.

Notice that in both macros, we loop through the rows from bottom to top. This is the best approach to check if a cell contains a text value or number value, and then delete the rows.

Key Points to Consider

The values that we deleted are in Column B in our dataset, so we passed 2 as the Column property inside the Cells function. Pass the Column number relevant to your dataset. For instance, if the value that you want to delete is in Column A then write If Cells(Wrk, 1), if it is in Column C then write If Cells(Wrk, 3), and so on.

Download Practice Workbook

Delete Cell Containing Value.xlsm

Get FREE Advanced Excel Exercises with Solutions!