How to Delete Rows If it doesn’t Contain Certain Text in Excel

,

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


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)

delete rows if do not contain certain text1

#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.

delete rows if do not contain certain text2

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

delete rows if do not contain certain text3

#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.

delete rows if do not contain certain text4

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

delete rows if do not contain certain text6

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

delete rows if do not contain certain text7

 

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

delete rows if do not contain certain text8

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.

delete rows if do not contain certain text9

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

delete rows if do not contain certain text10

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

delete rows if do not contain certain text11

#8 let’s see the result:

delete rows if do not contain certain text12

Video: Delete Rows That Do Not Contain Certain Text

 

 

Leave a Reply