Range object without blank cells
 

Hi

How do i call the following code, but not get back any empty cells

Set tempRange = Worksheets("Step Chart").Range("ColumnMapping")

As it returns all the cells, even if they are blank/empty...

I can return just the empty cells by using
..SpecialCells(xlCellTypeBlanks)
What is the reverse of this?

As in the end i want an array without any empty values, if there is any
in the named range.

Range object without blank cells
 

RMJ,

If you have only constansts, use

Worksheets("Step Chart").Range("ColumnMapping").SpecialCells(xlCell TypeConstants)

If you have only formulas, use

Worksheets("Step Chart").Range("ColumnMapping").SpecialCells (xlCellTypeFormulas)


If you have mixed formulas and constants, you need

With Worksheets("Step Chart").Range("ColumnMapping")
Set tempRange = Union(.SpecialCells(xlCellTypeConstants), .SpecialCells(xlCellTypeFormulas))
End With


HTH,
Bernie
MS Excel MVP


"RMJames" wrote in message
oups.com...
Hi

How do i call the following code, but not get back any empty cells

Set tempRange = Worksheets("Step Chart").Range("ColumnMapping")

As it returns all the cells, even if they are blank/empty...

I can return just the empty cells by using
.SpecialCells(xlCellTypeBlanks)
What is the reverse of this?

As in the end i want an array without any empty values, if there is any
in the named range.