Fast Way to Find and Delete Excel Rows

Find and delete Excel rows

It’s finally summer, and you need to stay cool, even when you’re using Excel. Here’s an energy-efficient and fast way to find and delete Excel rows. You can select several rows that contain similar data, and delete them all at the same time.

Find All the Data

In the worksheet shown below, there is a list of products sales, and a few of the records are for paper sales.

I’d like to delete those paper sales rows, without having to sort the worksheet, or spend a long time manually selecting the rows.

Find and delete Excel rows

Find the Paper Rows

To find all the Paper sales rows, I can use the Excel Find command. Here are the steps to do that:

    • On the Ribbon’s Home tab, click Find & Select, and then click Find.

FindAllDelete01

  • In the Find and Replace dialog box, type “paper” in the Find What box.
  • Click Find All, to see a list of all the cells that contain the text, “paper”
  • Select an item in the list, and then press Ctrl+A, to select the entire list. That will also select all the “paper” cells on the worksheet.

FindAllDelete02

Delete the Selected Rows

To delete the entire row for each “paper” cell that was found, follow these steps:

  • On the Ribbon’s Home tab, click Delete, and then click Delete Sheet Rows.

All the selected rows will be deleted, and the other product orders remain on the worksheet.

FindAllDelete03

Video: Find and Delete Excel Rows

To see the steps to find all the instances of a word, and delete the selected rows, watch this short Excel video tutorial.

More Find and Replace Examples

See more ways to use the Find and Replace commands in Excel, on my Contextures website.

Also, see how to select rows based on their conditional formatting colour, and delete the filtered rows. This example uses a list in a named Excel table.

___________

71 thoughts on “Fast Way to Find and Delete Excel Rows”

      1. That will be a problem if theres about tens and thousands of data and you have to filter them 1 by 1…

  1. Saved me hours of time and this was the first website I goodled on the subject.
    Can’t thank you enough, you are a genius!

  2. Great tip Debra! I’m working on a similar problem except I’m trying to delete rows containing any one of about 200 keywords/phrases from a sheet with +250,000 rows in total. I tried recording your tip and it gave me the following code but when I replay it, it only deletes the first row containing the keyword. Any suggestions on how I can get it to work using VBA? Merry Christmas!
    Sub Macro1()
    Cells.Find(What:=”KEYWORD TO BE DELETED”, After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Selection.EntireRow.Delete
    End Sub

    1. Jeff,
      The VBA you recorded is not searching for all instances in the active worksheet. The code you posted above, searches for one instance of “KEYWORD TO BE DELETED”, activates that cell, then deletes only that Row. You could try:
      Sub Macro1()
      Last = Cells(Rows.Count, “COLUMN”).End(xlUp).Row
      For i = Last To 1 Step -1
      If (Cells(i, “COLUMN”).Value) = “KEYWORD TO BE DELETED” Then
      Cells(i, “A”).EntireRow.Delete
      End If
      Next i
      End Sub
      Replace COLUMN with the letter of the column “KEYWORD TO BE DELETED” is in.

      1. Krystal, I would be for ever grateful if you could explain a very similar problem to me. I have 7000 patents in an Exel 2010 worksheet. I have a list of 1000 of those patents to delete. The list of patents that I want to delete references the Publication Number. So, I want to some how look up those 1000 Publication Numbers in the worksheet of 7000 and delete the whole Row of information associated with that Publication Number. I can send you the Excel Workbook, I am sure that it would be clear to you what I want to do after seeing it. Thank you Thank you Thank you!

  3. How can this be done with newer versions of Excel? My version doesn’t show me all cells with the target word I am looking to delete.
    Thanks.

  4. Awesome. So simple – using only toolbar/ribbon commands. All the other solutions VBA. You saved me hours. Thumbs up and five stars to you.

  5. Debra,
    Doing a new search in the “find and replace” dialog, I find myself needing to clear the previous search terms, but there is no ctl-all (or triple click) function in these fields so I may highlight all and then clear the fields for new search terms.
    Is there a quick way to clear these fields?

  6. Wow, that sure worked. Now I need to remove all items priced at 30 dollars or less. Tried same method, but when I use find: 25 for example…it also included any price that had a 25 in it (125,425, etc) Tried changing number format but still highlights items I don’t want included. Any ideas? Thanks.

  7. Cool Solution but lacks something!
    What if you have a “Lists” sheet, where you have more lists/”tables” from left to right with spaces betveen them.
    In this case, if you delete total rowa, you destroy more lists/tables data!

  8. This is fantastic for word searches, but what if you want to delete rows of cells containing dollar amounts below $600.00? I was able to conditionally format the spreadsheet to highlight figures under $600.00 but can’t find a way to mass delete those that have been highlighted. Your help is appreciated.

    1. @Mrs. B, instead of using this method, you can apply a filter, then delete the filtered rows
      Assuming that your list is in a named Excel Table (http://www.contextures.com/xlExcelTable01.html), follow these steps
      Make a backup copy of your file first — just to be safe.
      1. Click the arrow in the heading for the column where you applied the conditional formatting
      2. In the drop down, click Filter by Color, and select the color that you used.
      3. Select the colored cells, and on the Ribbon’s Home tab, click the arrow under the Delete command
      4. Click on Delete Table Rows.
      5. Remove the filter, and check that all the other rows are still okay, and the colored cells have been deleted.
      If it doesn’t look right, click the Undo button, or press Ctrl + Z to undo the deletion.

  9. This process is exactly what I have been looking for except……. the string I’m looking to delete starts with *** which is the excel default for wildcard. When I do a find for *** it selects all the rows. Is there a way to find the characters ***?

  10. Thank you very much for sharing this technique, i really appreciate and it helped me and saved my time. thanks once again

  11. Great tip…didn’t know it was possible to “bulk delete” like that! Instead of deleting, is it possible to copy the selected rows to the clipboard, or extract the selected record to another worksheet? If not, what about the ability to highlight the selected rows in some fashion? (thus making it easier to identify the rows in a large spreadsheet)

  12. When I attempt to select all then delete entire row, if de-selects my selecions in the find and replace tool. any Idea?

  13. I’ve used this technique to find, select and format all the ‘found’ cells, but never used it to delete rows. 🙂
    Perhaps because since Excel 2010, I have used the Data > Remove duplicates command, which gives the same results — except if you want to
    (a) find and select the cells in a case-sensitive manner, or
    (b) find and select partial matches — in which case you need to be *extremely* careful, because Excel can find matching text in cells in ways that you may not have thought of!

  14. Hi
    Im using the online version of excel and when i do a ctrl f , it doesn’t have the same look and feel as what it used to have and it doesn’t also show the number of records that it retrieves. The dialog box that pops up doesnt show anything except to look down or up etc.. which is so pathetic. I cant do a ctrl a in the dialog box because there is nothing of that sort so I cant seem to collectively delete the rows that contain a certain word I dont want.
    Pls help. 16.0.9222.5051 is my online build version for excel.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.