Transfer Range to Array

[Steven Drenker]

I've forgotten how to transfer a range to an array without stepping through
the cells one at a time.

I know you can transfer an array to a range with TheRange.Value = MyArray as
long as the dimensions of the array and range match. And you can transfer a
one-dimensional array with
Range("A1:A100").Value = Application.WorksheetFunction.Transpose(MyArray)

Which transposes an array to a vertical column in Excel.

But I can't remember the reverse -- range --> array.

Thanks.

 

[Tom Ogilvy]

Dim myArray as Variant
Dim theRange as Range
set theRange = ActiveSheet.UsedRange
myArray = theRange.Value

will give a two dimensional array, dimensions are 1 based.