How to Delete All Rows Not Containing Certain Text in Excel?

In the following dataset containing information about some fruits, Apple is listed multiple times. Let’s delete all the rows except those containing Apple.

Excel Delete All Rows Not Containing Certain Text Dataset

Method 1 – Using the Filter Feature

Steps:

Select the column to apply the filter. Here, B5:B10.
Click on the Filter option from the Data tab.

Utilize Filter Option to Delete All Rows Not Containing Certain Text in Excel

A Filter will appear on the first cell of the selection.

Click the Filter arrow and check all the texts except the one you want to keep (Apple).
Click OK.

All the rows not containing Apple are displayed.

Select the rows and delete them from the context menu, leaving the header.

Click the Filter arrow again.
Check (Select All) and click OK.

The final output will appear as in the image below.

Method 2 – Using VBA Code

Steps:

Press Alt + F11 to open the Microsoft Visual Basic Applications window.
Click Insert and select Module from the drop-down to open a module.

Delete All Rows Not Containing Certain Text Through Excel VBA

Copy the following code and paste it in the Module window:

Sub DeleteRowsNotContainingParticularText()
    Set nwRange = Application.Selection
    Set nwRange = Application.InputBox("Choose Range", "DeleteRowsNotContainingParticularText", nwRange.Address, Type:=8)
    srTxt = Application.InputBox("Input a Certain Text", "DeleteRowsNotContainingParticularText", "", Type:=2)
    For p = nwRange.Rows.Count To 1 Step -1
        Set nwRow = nwRange.Rows(p)
        Set nwCell = nwRow.Find(srTxt, LookIn:=xlValues)
        If nwCell Is Nothing Then
           nwRow.Delete
        End If
    Next
End Sub

Excel Delete All Rows Not Containing Certain Text by Applying VBA

Press F5 to run the VBA code.

A dialog box opens.

Select the Range of your dataset.
Click OK.

Input the text you want to keep in the rows (Apple) and click OK.

All the rows not containing Apple will be deleted.

How to Delete All Rows Containing Certain Text in Excel

Now let’s use a similar VBA code to delete all the rows that do contain Apple.

Steps:

Follow the steps in Method 2 above to open a new Module window.
Place the following code in the module:

Sub DeleteRowsWithCertainText()
Dim x1 As Range
For Each x1 In Range("B4:B10")
    If x1.Value = "Apple" Then
        x1.EntireRow.Delete
    End If
Next x1
End Sub

How to Delete All Rows Containing Certain Text in Excel