How to Delete Row If Cell Contains Specific Values in Excel?

We will be using a sample dataset that contains the Name and Age of some people.

Method 1 – Using Find and Replace to Delete a Row If a Cell Contains Specific Text in Excel

We want to find and delete rows that have cells starting with “Mr.” in the Name column.

Steps:

• Press Ctrl + F to open with Find and Replace window.
• Type “Mr.” within the Find what bar.
• Click on the Find All option.
• Select one of the found results and then press Ctrl + A to select all the found results.
• Hit the Close option.

• Press the Ctrl + – buttons to open the Delete dialog box.
• Select the Shift cells up option and hit OK.

• The output will appear as below.

Method 2 – Utilizing AutoFilter to Remove an Excel Row If a Cell Contains a Certain Value

We will delete all the Excel records starting with “Ms. Liesel” in the Name column.

Steps: 

• Select the entire data table.
• Go to Data, then choose Sort & Filter, and select Filter.

• Click on the drop-down icon at the corner in the Name column.
• Go to Text Filters and choose the Begins With option.

• A dialog box named Custom AutoFilter will pop up on the screen.
• Type Ms. Liesel in the begins with bar and hit OK.

• Press the Ctrl + – buttons and a dialog box will appear.
• Hit the OK button.

• The output will look like the following image.

Method 3 – Deleting a Row If a Cell Contains Specific Values in Excel Using VBA Code

Case 3.1 – Delete a Row If a Cell Contains Specific Text

Steps:

• Press Alt + F11 to open the VBA window.
• Go to Insert and select Module to open a new module.

• Copy the following code:

Sub DeleteRowsContainingtext()
Dim A As Worksheet
Dim B As Integer
Dim Step As Long
Set A = Worksheets("VBA")
For B = A.Range("B5:C14").Rows.Count To 1 Step -1
If Application.WorksheetFunction.IsText(Cells(B + 2, 2)) = True Then
A.Cells(B + 2, 2).EntireRow.Delete
End If
Next
End Sub

• Paste it in the VBA editor and save it by pressing Ctrl + S.

• Hit the F5 key to run the code and you will get your desired output.

Case 3.2 – Delete a Row If a Cell Contains Number

We will delete any row if a cell in the column Age contains 17.

Steps:

• Press Alt +F11 to open the VBA window.
• Go to Insert and chopse Module to open a new module.
• Copy the following code:

Sub DeleteRowsContainingNumbers()
Dim A As Long
Dim B As Long
A = 1000
For B = A To 1 Step -1
If Cells(B, 3).Value = "17" Then
Rows(B).Delete
End If
Next
End Sub

• Paste it in the VBA editor and save it by pressing Ctrl + S.

• Hit the F5 key to run the code and you will get your desired output.

Things to Remember

• Press Ctrl + F to open the Find and Replace dialog box.
• Use Ctrl + – to delete cells or rows.
• Press Alt + F11 to open the VBA window.

Download the Practice Workbook

You May Also Like These

• How to Delete All Rows Below a Certain Row in Excel?
• How to Delete Rows in Excel with Specific Text?
• How to Remove Highlighted Rows in Excel?
• How to Delete Blank Rows in Excel?
• How to Delete Row If Cell Is Blank in Excel?
• How to Delete Empty Rows at the Bottom in Excel?
• How to Delete All Rows Not Containing Certain Text in Excel?
• How to Delete Rows Based on Another List in Excel?

<< Go Back to Delete Rows | Rows in Excel | Learn Excel