Back in September I posted about deleting Blank Rows. Here is another way to it with array formulas.

**Get the Cell Address**

Enter this formula into same number of cells as your range and push to

**Ctrl**,

**Shift**and

**Enter**. (Braces at either end will appear automatically as in the picture below)

{=ADDRESS(SMALL(IF(B2:B14="","",ROW()),ROW(INDIRECT("1:"&COUNTA(B2:B14)))),2)}

This formula returns the row number for non-blank cells in descending order with the

**SMALL**function. The

**ADDRESS**function does the rest. Adjust accordingly to get the right column or use this self -adjusting version.

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

**Get the Values**

Once you have the addresses, it's not to hard to get the values.

=IF(ISNA(C2),"",INDIRECT(C2))

Or you could just get the values from the start with this array formula.

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

This version ajusts for using different rows.

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

If you don't like those nasty

**#N/A**errors, you might want to consider using something like this to chase them away. (

**Conditional Formatting**can also be used)