Named range in an array

[Frigster]

Hi,

I have five cells on a row (A1:A5). This range is named "xyz".
How can I transform this range in an array with VB code?

Thank you.
Paolo.

 

[Tom Ogilvy]

dim v as Variant
v = Range("xyz").Value

for i = 1 to 5
msgbox v(i,1)
Next


If you want a 1D array

v = application.Transpose("xyz")

for i = 1 to 5
msgbox v(i)
Next

 

[Alan Beban]

dim v as Variant
v = Range("xyz").Value

for i = 1 to 5
msgbox v(i,1)
Next


If you want a 1D array

v = application.Transpose("xyz")'<---A typo:v = application.Transpose(Range("xyz"))

for i = 1 to 5
msgbox v(i)
Next

Alan Beban

 

[ccroche]

Sorry, I think there is again a typo ...

If you want a 1D array
v = application.WorkSheetFunction.Transpose(Range("xyz"))

Christian CROCHE

 

[Tom Ogilvy]

No, there is no requirement to use WorksheetFunction. No typo.

 

[JE McGimpsey]

One way:

Dim arr As Variant
arr = Range("A1:A5").Value