How to Find and Delete Rows in Excel (5 Ways)

People who are used to working with a large datasheet, are familiar with deleting rows from their datasheet. It is required when you have to remove the whole entity from your spreadsheet. It is the most laborious work to find any specific row from a vast datasheet. In this article, we will introduce you to some marvelous features of Excel to find and delete those rows. If you are also facing trouble in doing the same job, follow this article.

In the article, we will demonstrate 5 easy approaches by which you will be able to find and delete rows in your datasheet. We are using a data list of 10 rows containing information on fruit amount and delivery areas of the UK and the US. Our data is in the range of cells B5:D14.

1. Finding and Deleting Rows Based on a Cell Value in Excel

In this method, we will find the rows based on a cell value or any specific text and then we will delete those rows from our datasheet. Our data is in the range of cells B5:D14. We will delete those rows which have a cell value ‘Apple’. The steps of this method are given as follows:

📌 Steps:

• First of all, in the Home tab, go to the Editing option.
• Select Find & Select > Find.

• A dialog box will appear, entitled Find and Replace. You can also press Ctrl+F on your keyboard to launch this dialog box.
• Now, write down what you want to find in the empty box Find what. In our case, we write the fruit name Apple.

• After that, click on the Match case and click on Find All.
• 2 cells will show in the dialog box. Press Ctrl+A to select all the entities. You will also see that in the datasheet it will also show selected.

• Now, in the Home tab, select Cells > Delete > Delete Sheet Rows.

• Finally, you will see that those 2 rows disappeared from our datasheet.

Thus, we can say that our method has worked perfectly.

2. Creating Excel Table to Find and Remove Rows in Excel

In this approach, we will find our desired rows by creating a table and then delete those rows. We are using the same datasheet which we have done before. We will delete those rows which have a cell value ‘Apple’ in the column entitled Fruit. The procedure is explained below:

📌 Steps:

• Select the entire range of cells B5:D14.
• Now, in the Insert tab, select Tables > Table.

• A small dialog box, called Create Table, will appear.
• Then, click on My table has headers and OK.

• The table will be created. Now, click on the D drop-down arrow in the right bottom corner of the cell.
• Deselect the Select All option and only select the Apple option.

• After that, click OK to close the Filter option and you will find only the rows containing Apple remains in the table.

• Select both of them and right-click on your mouse.
• In the Delete option, select the Entire Sheet Row option.

• Those rows will disappear.

• Now, again click on the Filter icon in the right bottom corner of the cell titled Fruit.
• Then, select the Select All option to get the remaining rows back on your datasheet.

• You will get back the other rows back on your table.

In the end, we can say that our procedure has worked accurately and we get our desired result.

Read More: How to Delete Filtered Rows in Excel?

3. Finding and Deleting Rows Based on Numeric Values

This procedure has some similarities with the previous method. However, the row-finding criteria will be completely different. For this approach, we will filter out those rows, that contain a fruit amount of less than 3000KG. The steps of this process are given as follows:

📌 Steps:

• First, select the range of cells B5:D14.
• Create a table like the previous approach, from the Insert tab, select Tables > Table.

• A small dialog box, called Create Table, will appear.
• Then, click on My table has headers and OK.

• The table will be created.

• Now, click on the drop-down arrow in the right bottom corner of the cell, titled Amount.
• Now, select the right arrow in the Filter option and select Less Than. You will find many other filtering criteria, which you can use later according to your requirement.

• A small dialog box will appear. Write down 3000 in the empty box beside the is less than.

• Click OK to close the dialog box and you will find those fruit items whose amount is less than 3000.

• Now, select both rows and right-click on your mouse.
• From the Delete option, select the Entire Sheet Row option.

• The rows will disappear.
• Then, again click on the Filter icon in the right bottom corner of the cell called Amount.

• Select the Select All option to get the remaining rows back on your datasheet.
• Finally, You will get the other rows back on your table and now, the total number of rows will be 8.

In this way, we can find rows on numeric values and can delete them.

Read More: How to Delete Multiple Rows in Excel with Condition?

4. Searching for Blank Rows and Remove Them in Excel

The method will demonstrate to you how to find out the blank rows from a datasheet and delete them easily. Our dataset is in the range of cells B4:D17. In this datasheet, we have 3 blank rows on rows 7,12 and 15. The steps are described as follows:

📌 Steps:

• First of all, select the entire range of cells B4:D17.
• In the Home tab, select Editing > Find & Select > Go to Special.

• A dialog box entitled Go to Special will appear.

• Now, click on Blank and click OK at the bottom.
• You will see the blank rows are selected.

• Then, right-click on your mouse and select the Delete option.

• Another small dialog box called Delete will appear.
• Select the Entire Row option on that box and click OK.

• The blank rows will be deleted and the remaining dataset will move upward.

Applying this method you can find and delete blank rows from your datasheet.

5. Applying VBA Macro to Find and Delete Rows

Writing a VBA code can also help you to find and delete rows from an Excel datasheet. For this process, we are using the same datasheet which we have already used. Our dataset is in the range of cells B5:K86. The steps of this process are given as follows:

📌 Steps:

• To start the approach, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press Alt+F11 to open the Visual Basic Editor.

• A dialog box will appear.
• Now, in the Insert tab on that box, click Module.

• Then, write down the following visual code in that empty editor box.

Sub Delete_Rows_Specific_Cell_Value()
    Dim ROW As Long
    Dim Worksheet As Long
    ROW = 15
    For Worksheet = ROW To 1 Step -1
    If Cells(Worksheet, 2) = "Apple" Then
        Rows(Worksheet).Delete
    End If
    Next
End Sub

• Close the Editor Now from the View ribbon, click on Macros > View Macros.

• A new dialog box will appear. Select Delete_Rows_specific_Cell_Value.

• Click on the Run button to run this code.

At last, we can say that our visual code worked perfectly and we are able to find and delete our desired rows.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to find and delete your desired rows in your Excel spreadsheet without any difficulties. If you have any further queries or recommendations, please share them with us in the comments section below.

Related Articles

• How to Delete Hidden Rows in Excel?
• How to Delete Unused Rows in Excel?
• How to Delete Multiple Rows in Excel Using Formula?
• How to Delete Rows in Excel without Affecting Formulas?
• How to Delete Rows in Excel That Go on Forever?
• How to Delete Infinite Rows in Excel?
• How to Remove Rows Containing Identical Transactions in Excel?

<< Go Back to Delete Rows | Rows in Excel | Learn Excel