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

Get FREE Advanced Excel Exercises with Solutions!

While working with data in Excel, the deletion of an Excel row is quite a common phenomenon. However, there are a good number of methods that we can use to delete an Excel row If a cell contains specific text within it. Additionally, different approaches seem to work in different situations for specific values in cells. In this article, I will show 3 effective techniques to delete a row if a cell contains specific values in Excel with examples and proper illustrations.

Here, we will be using a sample dataset to demonstrate all the methods in this tutorial. However, it contains the Name and Age of some persons.

excel delete row if cell contains specific values dataset

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


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

Let’s say, we want to find and delete rows with certain records e.g., rows that have cells started with “Mr.” in the Name column. Hence, follow the steps below.

🔗 Steps:

  • First, 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 the CTRL + – button to open up the Delete dialog box.
  • After that, select the Shift cells up option and hit OK.

Use of Find and Replace

  • Lastly, the output will appear as below.

Use of Find and Replace option


2. Utilizing AutoFilter to Remove an Excel Row If Cell Contains Certain Value

You can also filter some rows and delete them. See the following example.

W will delete all the Excel records starting with “Ms. Liesel” in the Name column using the AutoFilter feature in Excel. Hence, follow the steps below.

🔗 Steps: 

  • Initially, select the entire data table.
  • Then, go to Data ▶ Sort & Filter ▶ Filter.

Use of AutoFilter to Remove an Excel Row If Cell Contains Certain Value

  • Next, click on the drop-down icon at the down-right corner in the Name column.
  • Afterward, go to Text Filters ▶ Begins With option.

Use of AutoFilter Feature in Excel

  • At this point, a dialog box named Custom AutoFilter will pop up on the screen.
  • Now, type Ms. Liesel within the begins with the bar and hit OK.

Use of AutoFilter Feature in Excel

  • After that, press the CTRL + – button and a dialog box like the below image will appear on the screen.
  • Lastly, just hit the OK button.

Use of AutoFilter Feature in Excel

  • Now, the output will look like the below image.

Use of AutoFilter Feature in Excel


3. Deleting Row If Cell Contains Specific Values in Excel Using VBA Code

In this section, we will show how to delete rows using VBA if any cell within it contains any text or number using VBA code.

3.1 Delete Row If Cell Contains Specific Text

In the first example, I will show how to delete rows based on condition, e.g., cells in the rows contain a specific text.

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

🔗 Steps:

  • Firstly, press ALT +F11 to open the VBA window.
  • Secondly, go to Insert ▶ Module to open a new module.

Delete Row If Cell Contains Specific Values in Excel Using VBA Code

  • Thirdly, 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
  • Fourthly, paste it on the VBA editor and Save it by pressing CTRL + S.

Delete Row If Cell Contains Specific Text with VBA

  • Finally, hit the F5 key to run the code and you will get your desired output.

Use of VBA


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. Hence, follow the steps below.

🔗 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 Specific Number with VBA

  • Finally, hit the F5 key to run the code and you will get your desired output.

Use of VBA


Things to Remember

  • Firstly, press CTRL + F to open the Find and Replace dialog box.
  • Secondly, CTRL + – is the hotkey for deletion.
  • Lastly, you can press ALT + F11 to open the VBA window.

Download Practice Workbook

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


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.


You May Also Like These


<< Go Back to Delete Multiple Rows Based on Condition | Delete Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo