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