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

Often, we need to delete all rows that don’t contain specific text in Microsoft Excel. However, it’s easier to clear the cell contents all at once based on conditions than to clear them one by one. Moreover, it is also a time-saving process. When an Excel dataset contains similar kinds of data, and after the application of those data, they kind of become irrelevant. So, we need to clear the cells that contain this irrelevant data and use the updated data. In this article, I’ll show you 2 suitable ways to delete all rows not containing certain text in Excel. Hence, read through the article and save time.

How to Delete All Rows Not Containing Certain Text in Excel: 2 Suitable Ways

In this part, I will demonstrate 2 simple but tricky ways to delete all rows not containing text in Excel. In the dataset, you will see information about some fruits. The information includes the name of the Items, Prices, and Quantity. However, you can notice that Apple is listed multiple times for various quantities. For example, I will show you how to clear other contents from this dataset, keeping Apple only. For the purpose of demonstration, I have used the following sample dataset.

1. Utilize Filter Feature to Delete All Rows Not Containing Certain Text in Excel

Fortunately, a Filter is a useful tool that helps us display only the specified values in Excel. Based on the filtered result, you can later edit, copy, chart, or print only the visible values. However, I will delete all rows that don’t contain the item “Apple” from the chosen dataset. Hence, read through the steps below to easily understand the process.

📌Steps:

• Initially, select the column where the filter item is placed. Here, I have selected B5:B10.
• Then, click on the Filter option from the Data tab.

• Next, a filter option will appear on the first cell of the selection.
• After that, select the option and check all the texts except the certain one you want to keep in the row.
• Now, press OK.

• Afterward, you will get all the rows not containing certain text.

• Now, select the rows and delete them from the context menu except the header.

• Furthermore, filter again and select certain texts, and press OK.

• At last, the final output will appear as in the image below.

Read More: How to Delete Rows in Excel with Specific Text?

2. Delete All Rows Not Containing Certain Text Through Excel VBA

Furthermore, a VBA code can also help you to delete all rows not containing certain text in Excel. A VBA code is necessary for this method. However, you can just copy the code and run it in your Excel worksheet. Hence, go through the following steps in order to complete the task properly.

📌Steps:

• Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
• Secondly, click the Insert button and select Module from the menu to create a module.

• Thirdly, a new window will open and write the following VBA macro in the Module.

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

• Fourthly, press the F5 key to run the VBA code and select the Range of your dataset.
• Now, hit OK.

• After that, input the certain text you want to keep in the rows and press OK.

• Finally, you will find all the rows not containing certain text will be deleted.

How to Delete All Rows Containing Certain Text in Excel

Last but not least, you can delete all rows containing certain text in Excel by applying a VBA code. However, the process is quite similar to the previous one. For the purpose of demonstration, I have used the same sample dataset. In this case, I will delete all the rows that contain Apple by applying a simple VBA code. Hence, follow the steps mentioned below.

📌Steps:

• First of all, follow the steps mentioned in the second method in this article to get the Module dialog box.
• Afterward, insert the following code into 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

• Similarly, press F5 to run the code, and all the rows containing Apple will be deleted.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Conclusion

These are all the steps you can follow to delete all rows not containing certain text in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

Related Articles

• How to Delete All Rows Below a Certain Row in Excel?
• How to Remove Highlighted Rows in Excel?
• How to Delete Blank Rows in Excel?
• How to Delete Row If Cell Contains Specific Values in Excel?
• How to Delete Row If Cell Is Blank in Excel?
• How to Delete Empty Rows at the Bottom in Excel?
• How to Delete Rows Based on Another List in Excel?

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