Removing Blanks

There are a number of different array formulas that you can use to achieve this.
Each one has its own list of advantages and disadvantages.


Manually

Highlight the cells containing the blank cells
Home Tab, Editing Group, Find & Select > GoTo Special
Select Blanks
This will select all the cells that are blank
Home Tab, Cells Group, Delete > Delete Cells, Entire Row


Example 1


assumes that there is nothing else in this column
can be dragged down
doesn't need to be on the same rows
only works on vertical lists


Example 2


can be used with other data in the column
can be dragged down
doesn't need to be on the same rows
only works on vertical lists


Example 3


must be entered as one array formula block and cannot be dragged down
must enter the row number to subtract from the current ROW (in this example 7)
doesn't need to be on the same rows
only works on vertical lists


Example 4

Thank you to Chip Pearson (cpearson.com/excel/NoBlanks.aspx)

can be dragged down
doesn't have to be on the same rows
only works on vertical lists


Example 5 - Horizontal


needs to be the in the same columns
can be dragged
only works on horizontal lists


Example 6

Use a VBA Function
Functions User Defined > REMOVESPACES



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