Range to array excluding null values

[avi]

Hello,

Is there a way to create an array from a range with the formula
(varArray = RangeAddress) but excluding empty cells in the range (ot
any value)

It can be done by looping, but I am looking for the quick formula way

Thanks
avi

 

[Bernie Deitrick]

avi,

No need to use an array. You can directly address the cells of the range using:

Dim mcC As Range
For Each myC In Range("D216").SpecialCells(xlCellTypeConstants, 23)
'whatever you want here
Next myC

 

[avi]

Thanks but I really need it as an array for the rest of my application
Avi

 

[Bernie Deitrick]

Avi,

Then you will need to loop through the cells as shown and assign the values
to the array, after using redim on the array to set the size.

Bernie