software

Hi

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

Set tempRange = Worksheets(quot;Step Chartquot;).Range(quot;ColumnMappingquot;)

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.RMJ,

If you have only constansts, use

Worksheets(quot;Step Chartquot;).Range(quot;ColumnMappingquot;).SpecialCells(xlCell TypeConstants)

If you have only formulas, use

Worksheets(quot;Step Chartquot;).Range(quot;ColumnMappingquot;).SpecialCells (xlCellTypeFormulas)If you have mixed formulas and constants, you need

With Worksheets(quot;Step Chartquot;).Range(quot;ColumnMappingquot;)
Set tempRange = Union(.SpecialCells(xlCellTypeConstants), .SpecialCells(xlCellTypeFormulas))
End WithHTH,
Bernie
MS Excel MVPquot;RMJamesquot; gt; wrote in message oups.com...
gt; Hi
gt;
gt; How do i call the following code, but not get back any empty cells
gt;
gt; Set tempRange = Worksheets(quot;Step Chartquot;).Range(quot;ColumnMappingquot;)
gt;
gt; As it returns all the cells, even if they are blank/empty...
gt;
gt; I can return just the empty cells by using
gt; .SpecialCells(xlCellTypeBlanks)
gt; What is the reverse of this?
gt;
gt; As in the end i want an array without any empty values, if there is any
gt; in the named range.
gt;