Warning: Invalid argument supplied for foreach() in /home/techrecipes/public_html/wp-content/themes/techrecipes/header.php on line 77

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.

  • Lee

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

    • baxtqushi

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

      • julie

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

  • http://www.blackbus.org Pete

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

  • Bob

    Thanks mate…. cheers!

  • Bob

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

  • Bob

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

  • Ann

    Thank you

  • meka

    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.

    • medam

      Thanks meka, it worked like charm!!!

  • http://twitter.com/cbaileyau Clark Bailey

    just what i needed, thanks

  • http://www.catkin-collection.co.uk/ James Ratsey

    Thanks – works a treat

  • Dan

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

    Thanks muchly.

  • pramod B

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

    • Chris

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

  • Nate S

    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.

  • Louis

    Thank you so much. It works.

    Louis

  • Peterd710

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

  • Nit

    Helpful

  • Jude24204

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

  • Vijay2345

    Thanks, that was big help.

  • Maneesh George

    Hey It really works

  • Subhadip82

    sort the data by the row

  • Jaitken0308

    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?

  • Zinahewitt

    Sub deleteBlankRows()

    ‘ deleteBlankRows Macro
    ‘ Delete blank rows


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

  • Rajen Lama

    Rajen Lama
    Thanks Pete it works wonder

  • Kate

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

  • shereen

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

  • shereen

    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

  • james

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

  • Phil

    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?

  • Sreedhar

    Excellent..Thank you.

  • hypocrates

    thank you sir!!

  • orlando

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

  • Brian

    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.

  • Anne-Marie Freeman

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

  • Ned

    This is terrific.

    Thank you!

    Ned

  • Maria

    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.

  • Alex Caceres

    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!

  • MANISH PATWAL

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