Delete an Excel Row If a Cell Contains Specific Values (3 Methods)

While working with data in Excel, the deletion of an Excel row is quite a common phenomenon. There are a good number of methods that we can use to delete an Excel row If a cell contains a specific value within it. Different approaches seem to work in different situations. This article will show 3 effective techniques to delete a row in Excel If a cell contains specific values with examples and proper illustrations.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


3 Methods to Delete Excel Row If Cell Contains Specific Values

We will be using a sample Personal Info. Database as a dataset to demonstrate all the methods in this tutorial.

Delete an Excel Row If a Cell Contains Specific Values dataset

So, without having any further discussion let’s get into all the methods one by one.


1. Use of Find and Replace to Delete Row If Cell Contains Specific Text in Excel

Let’s say, we want to delete all the records that are started with “Mr.” in the Name column. To do so,

🔗 Steps:

❶ Press CTRL + F to open with Find and Replace window.

❷ Then type “Mr.” within the Find what bar.

❸ After that click on the Find All option.

❹ Now select one of the found results and then press CTRL + A button ▶ to select all the found results.

❺ As you’ve successfully selected all the found results, now hit the Close option.

Use of Find and Replace to Delete Row If Cell Contains Specific Text in Excel

❻ Now press CTRL + – button to open up the Delete dialog box.

❼ Select the Shift cells up option and hit OK.

That’s it.

2. Use of AutoFilter to Remove an Excel Row If Cell Contains Certain Text/Number

2.1  Delete Row If Cell Contains Specific Text

In this method, we will delete all the Excel records started with “Ms. Liesel” in the Name column using the AutoFilter feature in Excel. To do so,

🔗 Steps: 

❶ Select the entire data table.

❷ Go to Data ▶ Sort & Filter ▶ Filter.

Use of AutoFilter to Remove an Excel Row If Cell Contains Certain Text/Number

❸ Click on the drop-down icon at the down-right corner in the Name column.

❹ Go to Text Filters ▶ Begins With option.

Delete Row If Cell Contains Specific Text

At this point, a dialog box name Custom AutoFilter will pop up on the screen.

❺ Now type Ms. Liesel within the begins with bar and hit OK.

❻ After that press CTRL + – button and a dialog box like the below image will appear on the screen.

❼ Just hit the OK button. That’s simply it.


2.2  Delete Row If Cell Contains Number

In this method, we will delete all the Excel records greater than 23 in the Age column using the AutoFilter feature in Excel. To do so,

🔗 Steps: 

❶ Select any cell in the Age column.

❷ Go to Data ▶ Sort & Filter ▶ Filter.

❸ Click on the drop-down icon at the down-right corner in the Age column.

❹ Go to Number Filters ▶ Greater Than option.

Delete Row If Cell Contains Number

❺ Type 23 within the is greater than box and hit OK.

❻ Now press CTRL + – to delete all the filtered results and then hit the OK button from the pop-up dialog box.

That’s it.


3. Delete Row If Cell Contains Specific Text/Number in Excel Using VBA Code

In this section, we will delete a row if any cell within it contains any text or number using VBA code.

3.1  Delete Row If Cell Contains Specific Text

In this method, we will try to delete a row having age 17 in the Age column.

🔗 Steps:

❶ Press ALT +F11 to open the VBA window.

Delete Row If Cell Contains Specific Text/Number in Excel Using VBA Code

❷ Now go to Insert ▶ 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 on the VBA editor and Save it by pressing CTRL + S.

Delete Row If Cell Contains Specific Text

❺ Now go back to the worksheet called “VBA” and press the ALT + F8 button.

❻ Select the function name called DeleteRowsContainingtext() and click Run.

That’s it.


3.2  Delete Row If Cell Contains Number

In this method, we will discuss how you can actually delete any row if any cell in column Age contains any number within it.

🔗 Steps:

❶ Press ALT +F11 to open the VBA window.

❷ Now go to Insert ▶ 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 on the VBA editor and Save it by pressing CTRL + S.

Delete Row If Cell Contains Number

❺ Now go back to the worksheet called “VBA (2)” and press the ALT + F8 button.

❻ Select the function name called DeleteRowsContainingNumbers() and click Run.

That’s it.


Things to Remember

📌 Press CTRL + F to open the Find and Replace dialog box.

📌 CTRL + – is the hotkey for deletion.

📌 You can press ALT + F11 to open the VBA window.


Conclusion

To wrap up, we have illustrated 3 different methods, to delete a row if a cell contains a specific value in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


Read More

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo