Fast Way to 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 way to 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 are products sales, and a few of the records are for paper sales. I’d like to delete those rows, without having to sort the worksheet, or spend a long time manually selecting the rows.

FindAllDelete00

To find all the Paper sales rows, I can use the Find command.

  • 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 cells with "paper"
  • Select an item in the list, and press Ctrl+A, to select the entire list, and to 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:

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

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

FindAllDelete03

Watch the Video

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

___________

You may also like...

66 Responses

  1. Charlie Trig says:

    This saved me an incredible amount of time; thank you!

  2. Justin says:

    So simple, so effective…THANK YOU!

  3. james says:

    Is there a way to do the opposite? Find rows with a certain word, and purge everything else?

  4. nate park says:

    thans! it was amazing

  5. Laura says:

    HUGE time saver!! Thank you for posting!

  6. vh says:

    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!

  7. Tommy Löfgren says:

    Thank you! This saved me a lot of time (more than 3 hours perhaps)!

  8. Sharon Ortiz says:

    This cut my work time in half. Thank you very much!

  9. leeza says:

    Thank you for sharing. it helps me a lot!!

  10. Reza says:

    Fantastic

    Thank you

  11. Jeff C says:

    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

    • Krystal H says:

      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.

      • Gary says:

        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!

  12. Mo says:

    This was the only search result that gave me the simple solution. Thank you!!!

  13. Heena says:

    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.

  14. Maury says:

    Debra, you are now my favorite person ever. Hours of work saved with your instructions. Thank you.

  15. Mayank says:

    THanks

  16. Pramod Kumar Rai says:

    Thanks great tip. It saved a lot of my precious time.

  17. Kelvin says:

    Thanks Debra … tip for Excel moron greatly appreciated :-)

  18. peter says:

    Thank you, who knew there were so many choices to get to doing something so basic!

  19. Alli says:

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

  20. Ben says:

    Brilliant Solution! So simple without having to use VBA – Thanks!

  21. Diana says:

    Thank you! Very helpful!

  22. Jeff says:

    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?

  23. Karina says:

    Hi Debra!

    Would you happen to know how this is done in Excel for Mac?

    Thanks!

  24. Nandy says:

    Really Nice.Helped me lot.Thanks For the post

  25. Tiny says:

    Thank you very much, as others have said, you saved me an incredible amount of time!

  26. Justin says:

    This is very helpful! Thanks.

  27. Greg says:

    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.

  28. Nicole B says:

    Debra – somewhat related, you can find excel files from the web by using http://www.findthatexcel.com search engine

  29. Rpee says:

    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!

  30. Ralph says:

    This tip ROCKS! Thank you!

  31. Kris says:

    Thx, its very useful for my works.

  32. Adnan says:

    Why not just put a filter on paper and delete the rows. I think that’s quicker.

  33. Nilesh Talaviya says:

    Really, hats off to you; I save a lot of time with using this technique. Debra Dalgleish :)

  34. John says:

    Thank you very much :)!

  35. Mrs. B says:

    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.

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

  36. Vishal says:

    it is quite simple. It saved my lot time. Thanks for sharing this.

  37. sirisha says:

    Thank u so much.

  38. German says:

    Champion!

    Thanks

  39. Muhammad Adil says:

    Thank you so much!! it was very helpful.

  40. Tony Freeman says:

    Does anyone know how to move search rows into a new sheet instead of deleting them.?

  41. jeff rust says:

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

  42. PRASAD says:

    VERY NICE

  43. Muhammad Mubasher says:

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

  44. John F. says:

    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)

  45. Anushree says:

    Thank you !!!

  46. Michael says:

    This tip saved me a lot of time. Thank you!

  47. joe says:

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

  48. Ritanjali says:

    Thank you for sharing the tip.

  49. Kishor says:

    Its amazing. Saved my time. I am very thankful to You.

  50. Shirley says:

    Thank you!!!

  51. 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!

  52. Dario says:

    Thank you!

  53. P says:

    Is it just me or is anyone else having trouble selecting All from the find list using Ctrl + A ?

  54. tulpoeid says:

    Thank you!

Leave a Reply to tulpoeid Cancel reply

Your email address will not be published. Required fields are marked *