Selecting non-blanks cells only

  • Thread starter Thread starter nir020
  • Start date Start date
N

nir020

I have created a formula in excel that generates a range of cells. Within
that range there are some blank cells.

I wish to create a formula that looks in this range of cells and selects
only the cells with values in them, ignoring the blanks. It returns these
values in a new range with no blanks.

Can someone help?

Thanks

Nick
 
Nick,

Array enter (enter using Ctrl-Shift-Enter)

=INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($A$1:A1)))

Replace the three instances of $A$1:$A$10 with the address of your 'range of cells'

Then copy down until you get an error.

HTH,
Bernie
MS Excel MVP
 
Back
Top