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.
So how to select only visible cells, or in other words how to not select hidden cells, rows or columns while copying?
In MS Excel 2007, to add the Select Visible Cells Command, do the following steps:
Step 1: Right click on the toolbar on top and click Customize Quick Access toolbar.
Step 2: Under Choose Command From drop down menu, select Commands Not in the Ribbon.
Step 3: Scroll down and click on Select Visible Cells.
Step 4: Click on Add and OK.
