How to Delete Empty Rows those are in between in MS Office Excel?

By Mr.Byte • February 28, 2008

If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..

This tip is to delete the rows which are empty or blank based on the cells from single column. These empty or blank cells may lie in between different rows. This tip will find those empty cells and delete the whole row. It is applicable to both Excel 2003 and Excel 2007. There are macros to do this, but this is a simple step procedure without macros.

So how to delete in between empty rows in Excel?

Note: Make sure you already don’t have any cells or rows hidden. If you have hidden items, you may lose that data.

Step 1: Select the column from which you want to choose those empty or blank cells. (Let’s say Column A)
Step 2: Press F5 (Function Key). Click on Special and Select Blanks and Click OK.

Step 3: The Blank Cells will be highlighted now.

Step 4: Use the keyboard shortcut key (Ctrl + 9) to hide the rows which has highlighted cells. (Click here for keyboard shortcuts for hiding and unhiding rows and columns in Excel.)

Step 5: Use Select Visible Commands, to select only rows that are visible and Press Ctrl + C to copy the entire worksheet. This doesn’t copy the hidden rows. (Select Visible Command is a hidden option in Excel, if you don’t know how to use it or where it is, read ‘How to select only visible cells in Excel?’)

Step 6: Paste (Ctrl + V) it in a new sheet. Now you have a new sheet which doesn’t have any empty rows. You can delete the old sheet if you want!

Categories: How To, Software, Tips, Tutorial
Tags: Excel, Keyboard Shortcut Keys, MS Office, Office 2007

Share and Enjoy:

This is not the post you were looking for? Why not search within the site to find it!

Find us on Facebook

Subscribe:

Enjoyed this article? Every day we write many tips like this for Windows, Microsft Office Applications and other desktop applications.

Now you can get our tips daily in your inbox for free.
Feed Reader, Email or Twitter.

12 Responses to “How to Delete Empty Rows those are in between in MS Office Excel?”

How to Select and Copy only Visible Cells, Columns or Rows in MS Excel 2007 and 2003?
February 28, 2008 at 4:08 am | Permalink

[...] Update: One of the useful tip where you can use this feature is to delete empty rows in between in Excel. [...]
Keyboard Shortcut to Hide and Unhide Rows or Columns in Excel
February 28, 2008 at 10:36 pm | Permalink

[...] I found these shortcuts when I was trying to figure out how to delete empty rows in excel [...]
mohd kasim
March 11, 2008 at 6:00 am | Permalink

By grace of God you have solved my problem, God bless you,

regards

kasim
Ahmed
March 19, 2008 at 9:06 am | Permalink

neat ,smart, simple sulotion

i used it to delete empty columns in between data, but after i highlighted empty cells in columns ,i just delete them without making them hidden first.

thanks again
eric
March 26, 2008 at 9:34 am | Permalink

nice! i’ve been looking or this kind of tutorial! thanks. btw, can you still post the macro for this one, please?
niranjan
May 8, 2008 at 6:08 am | Permalink

thanks
Mike
September 22, 2008 at 11:30 pm | Permalink

All the hassles I have had in the past – deleting multiple blank rows manually – fixed in 3 key strokes.

Thanks – you are a star!!!
jim
September 30, 2008 at 5:50 am | Permalink

or, from step 3 (once the blanks are highlighted), go to EDIT > DELETE… > select your shifting preference. job done.
Alyson
November 6, 2008 at 7:38 pm | Permalink

Thank you!! You have saved me much time.
tanden511
November 14, 2008 at 3:11 am | Permalink

thansk in advance.
cva
February 3, 2009 at 4:25 am | Permalink

Thank you a lot.
caitlyn
September 30, 2009 at 4:17 pm | Permalink

THANK YOU THANK YOU…I JUST WENT FROM 2539 ROWS TO 883!!