How to Delete Empty Rows those are in between in MS Office Excel?
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!
[...] Update: One of the useful tip where you can use this feature is to delete empty rows in between in Excel. [...]
[...] I found these shortcuts when I was trying to figure out how to delete empty rows in excel [...]
By grace of God you have solved my problem, God bless you,
regards
kasim
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
nice! i’ve been looking or this kind of tutorial! thanks. btw, can you still post the macro for this one, please?
thanks
All the hassles I have had in the past – deleting multiple blank rows manually – fixed in 3 key strokes.
Thanks – you are a star!!!
or, from step 3 (once the blanks are highlighted), go to EDIT > DELETE… > select your shifting preference. job done.
Thank you!! You have saved me much time.
thansk in advance.
Thank you a lot.
THANK YOU THANK YOU…I JUST WENT FROM 2539 ROWS TO 883!!
There is an easier solution.
GO to Data, Sort by the column u want to remove the blanks from.
Then.. after that.. all the blanks will be pushed to the bottom of the list.
Its much easier to delete the columns after that.
If you want to arrange back to the original way, simply start with a new numbered column before u sort them by Data.
Very excellent fact i have learned form you site.
Thanks for your ready answer for me.
I keep touch with site for the doubt clarification.
Thank you sooooooooo much!
That’s nice and all but what if I have a 5-tab workbook with 30 pages worth of info and 9 pages worth of blank cells? Copying and pasting isn’t the most efficient thing to do. I’m looking for a way to delete the 9 pages worth of empty cells from printing or even showing up in the print preview pane WITHOUT hiding. Deleting them is the only they won’t add size to my workbook.
“GO to Data, Sort by the column u want to remove the blanks from.
Then.. after that.. all the blanks will be pushed to the bottom of the list.”
Sweet! Good tip.