Blank Cells Vertical


Single Column - GoToSpecial

Highlight the cells containing all the cells, including the blank cells.
(Home tab, Editing Group)(Find & Select - GoTo Special) or (F5, Special).
This will display the "Go To Special" dialog box.
Select Blanks and press OK.
This will select all the cells that are blank.

Then remove the entire rows for all the selected cells.
(Home Tab, Cells Group)(Delete > Delete Cells, Entire Row).
Right mouse click and press Delete.
This will display the Delete dialog box.
Choose "Shift Cells Up" and press OK.
The Undo (Ctrl + Z) can be used if you want to put the blank cells back.


Single Column - Find Dialog

Highlight the cells containing all the cells, including the blank cells.
(Home tab, Editing Group)(Find & Select - Find) or (Shift + F5).
This will display the "Find and Replace" dialog box.
Leave the "Find What" box blank and press "Find All".
Select (Ctrl + A) to select all the occurrences and press OK.
This will select all the cells that are blank.

(Home Tab, Cells Group)(Delete > Delete Cells, Entire Row).
Right mouse click and press Delete.
This will display the Delete dialog box.
Choose "Shift Cells Up" and press OK.
The Undo (Ctrl + Z) can be used if you want to put the blank cells back.


Single Column - Array Formula

An alternative to actually removing the blank cells, is to create another list without the blanks.
This can be done using Array Formulas and there are several different formulas you could use.
Formulas > Array Formulas > Removing Blanks


Multiple Columns

The steps would be exactly the same as the single column
Instead of selecting one column you can select multiple columns.
These columns do not have to be next to one another (contiguous)


From A Table

You can only delete entire columns from a table, not individual cells.


VBA User Defined Function

NONBLANKVALUES - Returns a cell range with all the blanks removed.


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext