Blank Cells Vertical
Single Column - Manually
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 - Formula (alongside)
A formula can be used if you want to keep the original list.
This formula will display the list (without the blanks) alongside the original list (using the same rows).
=IFERROR(INDEX(B$2:B$12,SMALL(IF(B$2:B$12<>"",ROW(B$2:B$12)-ROW(B$2)+1,""),ROWS(B$2:B2))),"")
Place this formula in cell "D2" and press Enter.
Drag this cell down to cell "D12".
![]() |
Single Column - Formula (underneath)
This formula will display the list (without the blanks) underneath the original list (using the same column).
=IFERROR(INDEX(B$2:B$12,SMALL(IF(B$2:B$12<>"",ROW(B$2:B$12)-ROW(B$2)+1,""),ROWS(B$14:B14))),"")
Place this formula in cell "B14" and press Enter.
Drag this cell down to cell "B24".
![]() |
VBA User Defined Function
NONBLANKVALUES - Returns a cell range with all the blanks removed.
© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited TopPrevNext