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.


Download Practice Workbook

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


5 Easy Methods to Find and Delete Rows in Excel

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 the information on fruit amount and delivery areas of the UK and the US. Our data is in the range of cells B5:D14.


1. Find and Delete 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.

Find and Delete Rows Based on a Cell Value in Excel

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

Find and Delete Rows Based on a Cell Value in Excel

  • 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 shown selected.

Find and Delete Rows Based on a Cell Value in Excel

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

Find and Delete Rows Based on a Cell Value in Excel

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

Find and Delete Rows Based on a Cell Value in Excel

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

Read More: How to Remove Blank Rows in Excel (7 Methods)


2. Creating 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.

Creating Table to Find and Delete Rows in Excel

  • 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.
  • Deselect the Select All option and only select the Apple option.

Creating Table to Find and Delete Rows in Excel

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

Creating Table to Find and Delete Rows in Excel

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

Creating Table to Find and Delete Rows in Excel

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

Creating Table to Find and Delete Rows in Excel

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

Creating Table to Find and Delete Rows in Excel

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

Read More: How to Remove Table Functionality in Excel (3 Methods)


3. Find and Delete 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 contains 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.

Find and Delete Rows Based on Numeric Values

  • 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 the Less Than. You will find many other filtering criteria, which you can use later according to your requirement.

Find and Delete Rows Based on Numeric Values

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

Find and Delete Rows Based on Numeric Values

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

Find and Delete Rows Based on Numeric Values

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

Find and Delete Rows Based on Numeric Values

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

Find and Delete Rows Based on Numeric Values

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

Find and Delete Rows Based on Numeric Values

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

Read More: How to Remove Value in Excel (9 Methods)


Similar Readings


4. Search for Blank Rows and Remove Them

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.

Find and Delete Blank Rows in Excel

  • A dialog box entitled Go to Special will appear.

Find and Delete Blank Rows in Excel

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

Find and Delete Blank Rows in Excel

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

Find and Delete Blank Rows in Excel

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

Find and Delete Blank Rows in Excel

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

🔍 Things You Should Know:

You can also delete the blank row by creating a table shown in method 2 (Creating a Table to Find and Delete Rows in Excel). In this case, you to select the Blank option instead of others.

Read More: How to Remove Blank Rows in Excel Pivot Table (4 Methods)


5. Embedding VBA Code to Delete Rows in Excel

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:

  • For starting 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. Or You can also press Alt+F11 for opening the Visual Basic Editor.

VBA to Find and Delete Rows

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

VBA to Find and Delete Rows

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

VBA to Find and Delete Rows

  • Click on the Run button to run this code.

VBA to Find and Delete Rows

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

Read More: How to Delete Empty Rows and Columns in Excel VBA (4 Methods)


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.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo