Remove Blanks

Get the Cell Addresses only

To get the cell addresses only from range A1:A10, enter this formula in cells B1:B10 as an array formula. (Push Ctrl, Shift and Enter at the same time)

{=ADDRESS(SMALL(IF(A1:A10="","",ROW()),ROW(INDIRECT("1:"&COUNTA(A1:A10)))),COLUMN(A1))}

Cells with values will be shown in descending order, blank rows will be shown as #N/A.

You can get rid of the #N/A errors by entering this formula like this in cell C1 and dragging down to C10.

=IF(ISNA(B1),"",B1)

Get the Values only

To get the values only from range A1:A10, enter this formula in cells B1:B10 as an array formula. (Push Ctrl, Shift and Enter at he same time)

{=INDEX(A1:A10,SMALL(IF(A1:A10="","",ROW()),ROW(INDIRECT("1:"&COUNTA(A1:A10))))-(ROW()-1))}

Cells with values will be shown in descending order, blank rows will be shown as #N/A.

You can get rid of the #N/A errors by entering this formula like this in cell C1 and dragging down to C10.

=IF(ISNA(B1),"",B1)