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


"nir020" wrote in message
...
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