Range object without blank cells

R

RMJames

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.
 
B

Bernie Deitrick

RMJ,

If you have only constansts, use

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

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
 
Top