Range to array excluding null values

  • Thread starter Thread starter avi
  • Start date Start date
A

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
 
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("D2:D16").SpecialCells(xlCellTypeConstants, 23)
'whatever you want here
Next myC
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I exclude a cell from a range? 4
Select range through Inputbox 2
Creating a truncated range- Speed issue 1
Arrays and scattered cell values or another way? 16
copy a 2 dimensional array to a range 2
VBA set thick borders around non empty cells in range 0
Baffled with assignment of range to array why won't it work??? Anyhelp appreciated 4
(Newbie) Copying values from an array to the worksheet 1

Back
Top