Multiple ranges in an array

[Zygor]

Can anyone help me with some syntac in Excel. I am trying to hard code an array with various ranges.

As an example -
Dim varArray As Variant

Set varArray = Array(Range("A1:e5"), Range("G11:G22"))

(This throws a type mismatch error)

 

[SkipVought]

Hi,

Only OBJECTS get Set

Dim varArray As Variant

   varArray = Array(Range("A1:e5"), Range("G11:G22"))

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Zygor]

Doh! Thanks Skip!

 

[xlbo]

depending on what you are doing afterwards, you may be better off storing like:

Dim varArray As Variant

varArray = Array("A1:e5", "G11:G22")

You would then reference the range like:

set myRange = Range(varArray(1))





Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

or...

set myrange = union(Range("A1:e5"), Range("G11:G22"))

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}