Excel 2007: Eliminate Blank Rows in a Spreadsheet

Contributor Icon Contributed by Rob Rogers  
Tag Icon Tagged: Microsoft Excel  

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

 

27 Comments -


  1. Lee said on January 8, 2009

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

  2. Pete said on January 27, 2009

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

  3. Bob said on February 11, 2009

    Thanks mate…. cheers!

  4. Bob said on February 11, 2009

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

  5. Bob said on February 11, 2009

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

  6. Ann said on June 2, 2009

    Thank you

  7. meka said on October 29, 2009

    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.

  8. Clark Bailey said on January 11, 2010

    just what i needed, thanks

  9. James Ratsey said on January 15, 2010

    Thanks – works a treat

  10. Dan said on January 18, 2010

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

    Thanks muchly.

  11. pramod B said on April 29, 2010

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

  12. Chris said on April 29, 2010

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

  13. Nate S said on May 27, 2010

    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.

  14. Louis said on July 28, 2010

    Thank you so much. It works.

    Louis

  15. Peterd710 said on July 30, 2010

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

  16. Nit said on August 3, 2010

    Helpful

  17. Jude24204 said on August 5, 2010

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

  18. Vijay2345 said on August 11, 2010

    Thanks, that was big help.

  19. Maneesh George said on September 22, 2010

    Hey It really works

  20. Subhadip82 said on December 2, 2010

    sort the data by the row

  21. Jaitken0308 said on March 2, 2011

    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?

  22. Zinahewitt said on March 31, 2011

    Sub deleteBlankRows()
    ‘
    ‘ deleteBlankRows Macro
    ‘ Delete blank rows
    ‘

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

  23. Rajen Lama said on April 8, 2011

    Rajen Lama
    Thanks Pete it works wonder

  24. Kate said on August 24, 2011

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

  25. shereen said on October 25, 2011

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

  26. shereen said on October 25, 2011

    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

  27. Phil said on December 22, 2011

    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?

 

RSS feed for comments on this post. TrackBack URL

Leave a comment -