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.

If this article helped you, please THANK the author by sharing. Your participation helps us to help others.

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?

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.