This post will guide you how to delete rows if it does not contain a certain text value in Excel. How do I delete rows that do not contain specific text with VBA Macro in Excel. How to use Filter function to delete rows that do not contain a certain text in Excel.

• Delete Rows That Do Not Contain Certain Text
• Delete Rows That Do Not Contain Certain Text with VBA
• Video: Delete Rows That Do Not Contain Certain Text

Delete Rows That Do Not Contain Certain Text

---

If you want to delete rows that do not contain certain text in your selected range, you can use filter function to filter out that do not contain certain text, and then select all of the filtered rows, delete all of them. Just do the following steps:

#1 select one column which contain texts that you want to delete rows based on. (Assuming that you want to delete all rows that do not contain excel product name)

#2 go to DATA tab, click Filter command under Sort & Filter group. And one filter icon will be added into the first cell of the selected column.

#3 click Filter icon in the first cell, and checked all text filters except that certain text you want to contain in rows.

#4 all rows in which do not contain certain text value “excel” in Column A have been filtered out.

#5 select all of them except header row (the first row), and right click on it, and then select Delete Row from the drop down menu list.

#6 click Filter icon again, and select clear Filter From product. Click Ok button.

#7 all rows that do not contain certain text “excel” have been deleted.

 

Delete Rows That Do Not Contain Certain Text with VBA

---

You can also use an Excel VBA Macro to achieve the same result of deleting rows that do not contain certain text. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub DelRowsNotContainCertainText()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range which contain texts that you want to delete rows based on", "DelRowsNotContainCertainText", myRange.Address, Type:=8)
    cText = Application.InputBox("Please type a certain text", "DelRowsNotContainCertainText", "", Type:=2)
    For i = myRange.Rows.Count To 1 Step -1
        Set myRow = myRange.Rows(i)
        Set myCell = myRow.Find(cText, LookIn:=xlValues)
        If myCell Is Nothing Then
           myRow.Delete
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Select one Range which contain texts that you want to delete rows based on. click Ok button.

#7 Please type a certain text. click Ok button.

#8 let’s see the result:

Video: Delete Rows That Do Not Contain Certain Text