Excel 2007: Eliminate Blank Rows in a Spreadsheet

Blank rows can be beneficial when it comes to making your spreadsheet easier to read, however if you are going to pull the data into another application (such as Access), the blank rows can cause you some problems. You could go through the painful task of selecting and removing these rows individually, but if you are working with a large spreadsheet, it could take forever to accomplish this. Instead, utilize Go To Special and let Excel do the work for you. Here’s how:


1. Select the range of data that contains the blank rows.

2. Go to the Ribbon, and select the Home tab.

3. In the Editing section, click the small arrow next to Find & Select.

4. Select Go To Special.

5. Select the Blanks radio button and click OK.

6. All blank rows within the selected data range will be highlighted.

7. Go to the Ribbon.

8. Click the small arrow beneath Delete.

9. Select Delete Sheet Rows

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

40 Responses to “Excel 2007: Eliminate Blank Rows in a Spreadsheet”

  1. January 08, 2009 at 11:42 pm, Lee said:

    incorrent… it selects all empty _cells_, not rows, and then deletes any row that has at least one empty cell

    Reply

    • September 24, 2012 at 2:28 pm, baxtqushi said:

      You’re right. It’s better to highlight column where empty cells to be eliminated.
      step1 needs to be re-stated

      Reply

      • September 24, 2013 at 9:40 am, julie said:

        > Thanks. baxtqushi’s suggestion worked like a charm.

        Reply

  2. January 27, 2009 at 8:39 pm, Pete said:

    I used Data > Remove duplicates” It leaves you with only 1 blank row. This of course assumes you have no other duplicates.

    Reply

  3. February 11, 2009 at 1:34 pm, Bob said:

    Thanks mate…. cheers!

    Reply

  4. February 11, 2009 at 1:36 pm, Bob said:

    Sorry… agree with Lee, all rows with blanks are deleted and this is not what I want

    Reply

  5. February 11, 2009 at 1:39 pm, Bob said:

    Agree with Lee, can try sorting data on spreadsheet then all blank rows will be together where they can be deleted

    Reply

  6. June 02, 2009 at 8:22 pm, Ann said:

    Thank you

    Reply

  7. October 29, 2009 at 12:51 pm, meka said:

    Yes, this won’t work if you select the entire spreadsheet. Try only selecting one column that you know has data in every column you need saved. Then follow the steps above to delete the empty rows.

    Reply

    • December 03, 2012 at 11:12 am, medam said:

      Thanks meka, it worked like charm!!!

      Reply

  8. January 11, 2010 at 2:55 pm, Clark Bailey said:

    just what i needed, thanks

    Reply

  9. January 15, 2010 at 9:34 am, James Ratsey said:

    Thanks – works a treat

    Reply

  10. January 18, 2010 at 4:39 pm, Dan said:

    Only wanted blank cells in a column killing. Worked a treat.

    Thanks muchly.

    Reply

  11. April 29, 2010 at 9:06 am, pramod B said:

    very helpful, but is there any way to select blank rows by pushing one click?

    Reply

    • April 29, 2010 at 7:36 pm, Chris said:

      You could just use Autofilter and pick (Blank) from the column header and then highlight / delete the blank rows that way.

      Reply

  12. May 27, 2010 at 4:27 pm, Nate S said:

    I had over 200k rows in my spreadsheet so the technique above did not work for me. What I did do however is just a simple find and replace of blank rows into a unique tag. Then after sorting, I simply deleted all rows with that tag.

    Reply

  13. July 28, 2010 at 12:47 pm, Louis said:

    Thank you so much. It works.

    Louis

    Reply

  14. July 30, 2010 at 11:17 pm, Peterd710 said:

    This is very simple and effective way of deleting blank rows. It works for me fine. Thank you very much.

    Reply

  15. August 03, 2010 at 5:37 am, Nit said:

    Helpful

    Reply

  16. August 05, 2010 at 1:51 pm, Jude24204 said:

    Thanks dude.works great…It could helps me a lot…

    Reply

  17. August 11, 2010 at 2:23 pm, Vijay2345 said:

    Thanks, that was big help.

    Reply

  18. September 22, 2010 at 12:21 pm, Maneesh George said:

    Hey It really works

    Reply

  19. December 02, 2010 at 5:40 pm, Subhadip82 said:

    sort the data by the row

    Reply

  20. March 02, 2011 at 8:00 pm, Jaitken0308 said:

    Question:

    This works well, but I have many cells that use if statements to read info from other sheets. It goes something like this:
    “If this certain cell from this certain sheet is blank, do nothing, else do this”

    So many cells are “blank” but contain formulas in them. How can I select all the rows that “appear” blank and delete them?

    Reply

  21. March 31, 2011 at 4:35 pm, Zinahewitt said:

    Sub deleteBlankRows()

    ‘ deleteBlankRows Macro
    ‘ Delete blank rows


    Columns(“C:C”).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    End Sub

    Reply

  22. April 08, 2011 at 1:13 am, Rajen Lama said:

    Rajen Lama
    Thanks Pete it works wonder

    Reply

  23. August 24, 2011 at 9:14 am, Kate said:

    Thank you so much. This saved me tons of time!

    Reply

  24. October 25, 2011 at 2:19 am, shereen said:

    How do you find a blank row? Do you use Find/Replace?

    Reply

  25. October 25, 2011 at 2:39 am, shereen said:

    I need to delete a range of rows.. I used conditional formatting to select a set of rows, how do I delete – if i press delete – the header row is getting deleted

    Reply

  26. December 15, 2011 at 4:27 am, james said:

    That works. but i have some hidden rows which are important i want to save them.. advise something important.

    Reply

  27. December 22, 2011 at 6:14 am, Phil said:

    I’ve googled to my whit’s end, and all the websites give this same advice, but when I do it, it selects ONLY the first contiguous group of empty rows, and none of the other empty rows. Is there any way I can change that?

    Reply

  28. March 06, 2012 at 11:43 pm, Sreedhar said:

    Excellent..Thank you.

    Reply

  29. April 18, 2012 at 6:27 pm, hypocrates said:

    thank you sir!!

    Reply

  30. May 15, 2012 at 10:25 am, orlando said:

    Thanks a lot for the info. I’d add (to make the instruction dummyproof ;-) ) on step 7, ‘. Go to the “Cells” Ribbon.’

    Reply

  31. June 29, 2012 at 5:15 pm, Brian said:

    Wrong. I tried this and it says “no cells found”. Even if I select a bunch of empty cells in a brand new spreadsheet, it simply will not find them.

    Don’t know who thought this would work, but it doesn’t. Period.

    Reply

  32. July 12, 2012 at 11:34 pm, Anne-Marie Freeman said:

    You are brilliant! Worked like a charm. Saved me future hours of work as well. Thank you!

    Reply

  33. November 10, 2013 at 7:53 pm, Ned said:

    This is terrific.

    Thank you!

    Ned

    Reply

  34. November 17, 2013 at 10:44 am, Maria said:

    I had a cell with data in the first line and 3 blank rows following – because of formatting wouldn’t allow me to copy only data – when I deleted blank rows, it took everything. I cleared format from column using eraser icon, then with column highlighted, followed steps as above and it worked perfectly. thank you.

    Reply

  35. December 09, 2013 at 7:23 am, Alex Caceres said:

    In my case, this also selected the blank cells which had other data in the row, therefore not working. Not to mention, that I could not find the delete function in the ribbon (Excel 2007).

    I ended up creating quick filters, selecting ‘Blanks’ for the column with the most information, and then simply deleted all the selected blank rows. Make sure your selection do NOT contain any data.

    Hope this complements!

    Reply

  36. December 30, 2013 at 11:00 pm, MANISH PATWAL said:

    Dear, It really worked…. thanks a ton… it helped me a lot…

    Reply

Leave a Reply