loading multiple non-contiguous range into an array

[matelot]

Hi,
I have a selection range that is non-contiguous and want to load them into
an array. It only load the first range.
set rng = range("A1:C2,A9:C10,A15:C18")
myArray = rng.value

Anything wrong with the code? can you suggest on how to best load all the
ranges into an array.

Thanks

 

[Jan Karel Pieterse]

Hi Matelot,

I have a selection range that is non-contiguous and want to load them into
an array. It only load the first range.
set rng = range("A1:C2,A9:C10,A15:C18")
myArray = rng.value

Anything wrong with the code? can you suggest on how to best load all the
ranges into an array.

As you have discovered, you can only bulk-read contiguous ranges into an
array. You'll have to loop through the Areas collection of the range and add
each area's cells individually.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[matelot]

Jan,
I end up copy and paste the range into an spreadsheet. Then I read the
entire range into an array. That works for me.

Thanks.