Lyte Byte

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!