Named Range to Array Oddity
 

I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not
contiguous and when assigning it to the array the array only appears to
have used the second section of the range. Is this what normally
happens and is there a work around?

If I gave each part of the original range a separate name could I then
read them easily in to the same array? How?

Named Range to Array Oddity
 

Hi
could you provide the exact name definition and your assignment code.

Just as a guess: You have to use the areas collection to loop through all
areas of your range

"bloycee" wrote:

I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not
contiguous and when assigning it to the array the array only appears to
have used the second section of the range. Is this what normally
happens and is there a work around?

If I gave each part of the original range a separate name could I then
read them easily in to the same array? How?

Named Range to Array Oddity
 

I created the named range through the worksheet rather than VB so have
something like "MyNamedRange" is =Sheet1!$H$2:$K$13,Sheet1!$A$2:$D$21
from the Insert, Name, Define menu controls.

The assignment I tried was simply:

Dim MyArray as Variant

MyArray=Range("MyNamedRange").Value

This returned the data from cells H2 to K13 but not that in A2 to D21



Frank Kabel wrote:
Hi
could you provide the exact name definition and your assignment code.

Just as a guess: You have to use the areas collection to loop through
all
areas of your range

"bloycee" wrote:

I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not
contiguous and when assigning it to the array the array only
appears to
have used the second section of the range. Is this what normally
happens and is there a work around?

If I gave each part of the original range a separate name could I
then
read them easily in to the same array? How?

Named Range to Array Oddity
 

You can't assign a discontiguous range to an array directly

(using a command like varr = Range("MyArray").Value)

You generally can't append values to an array in bulk (as above).

you can loop through all the cells in the range and add them one at a time
to the array.

--
Regards,
Tom Ogilvy


"bloycee" wrote in message
oups.com...
I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not
contiguous and when assigning it to the array the array only appears to
have used the second section of the range. Is this what normally
happens and is there a work around?

If I gave each part of the original range a separate name could I then
read them easily in to the same array? How?

Named Range to Array Oddity
 

Hi
then see Tom's reply

"bloycee" wrote:

I created the named range through the worksheet rather than VB so have
something like "MyNamedRange" is =Sheet1!$H$2:$K$13,Sheet1!$A$2:$D$21
from the Insert, Name, Define menu controls.

The assignment I tried was simply:

Dim MyArray as Variant

MyArray=Range("MyNamedRange").Value

This returned the data from cells H2 to K13 but not that in A2 to D21



Frank Kabel wrote:
Hi
could you provide the exact name definition and your assignment code.

Just as a guess: You have to use the areas collection to loop through
all
areas of your range

"bloycee" wrote:

I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not
contiguous and when assigning it to the array the array only
appears to
have used the second section of the range. Is this what normally
happens and is there a work around?

If I gave each part of the original range a separate name could I
then
read them easily in to the same array? How?

Named Range to Array Oddity
 

This seemed to work for me in very limited testing

Sub test()
Dim v, i as Long, aCnt as long

'ActiveWorkbook.Names.Add Name:="myname", _
'RefersTo:=Range("Sheet1!$H$2:$K$13,Sheet1!$A$2:$D $21")
'
'For Each cell In Range("myname")
'i = i + 1
'cell.Value = i
'Next

With Range("myname")
aCnt = .Areas.Count
ReDim v(1 To aCnt) As Variant
For i = 1 To aCnt
v(i) = .Areas(i).Value
Next
End With

'test it
Dim rw As Long, cl As Long
For i = 1 To UBound(v)
Debug.Print "Area " & i
For rw = 1 To UBound(v(i), 1)
For cl = 1 To UBound(v(i), 2)
Debug.Print v(i)(rw, cl)
Next
Next
Next

End Sub

Regards,
Peter


"bloycee" wrote in message
oups.com...
I created the named range through the worksheet rather than VB so have
something like "MyNamedRange" is =Sheet1!$H$2:$K$13,Sheet1!$A$2:$D$21
from the Insert, Name, Define menu controls.

The assignment I tried was simply:

Dim MyArray as Variant

MyArray=Range("MyNamedRange").Value

This returned the data from cells H2 to K13 but not that in A2 to D21



Frank Kabel wrote:
Hi
could you provide the exact name definition and your assignment code.

Just as a guess: You have to use the areas collection to loop through
all
areas of your range

"bloycee" wrote:

I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not
contiguous and when assigning it to the array the array only
appears to
have used the second section of the range. Is this what normally
happens and is there a work around?

If I gave each part of the original range a separate name could I
then
read them easily in to the same array? How?

Named Range to Array Oddity
 

Thanks all,

I'll give Peter's idea a go.

Cheers.