Tiny Giant
0
Q:

excel vba create an array from a range

arrayData = Array("A", "B", "C", "D", "E")

[a1].Resize(UBound(arrayData)) = Application.Transpose(arrayData)
5
v = [a1:b20]

'If v is dimensioned as a variant then he above creates a 2d array, 
'20 rows high by 2 columns wide and those 40 array elements contain
'the values from the specified range.

'The square brackets are shorthand notation. Another way to code the
'same thing is:

v = Range("a1:b20")

'In both of the above examples, the Range object is returning its 
'default... the Range.Value property. But keep in mind that the
'Range.Value2 property is about 20% quicker. So it could be more
'performant to code these two examples like so:

v = [a1:b20].Value2
v = Range("a1:b20").Value2
4

New to Communities?

Join the community