Creating an Array from a Range using Offset

[Frank & Pam Hayes]

I am trying to create an Array from a Range of data in a spreadsheet. I am
able to do this successfully when I explicitly define the range, but I am
having touble doing it using a variable and the Offset command.

The following code works fine:

Sub RangeToArray()
Dim MyArray as Variant
MyArray = Sheets(1).Range("A1:B5")
End Sub

What I would like to do is something like this:

Sub RangeToArray()
Dim MyArray as Variant
Dim NumRows as Long
Dim NumCols as Long

NumRows = 5
NumCols = 2

MyArray = Sheets(1).Range("A1A1(offset(NumRows,NumCols)"))

End Sub

I just can't seem to get the syntax correct. I sure would appreciate any
help from the group.

Frank Hayes

 

[Alan Beban]

MyArray = Sheets(10).Range("A1:" & Range("A1").Offset(numRows, _
numCols).Address)

Alan Beban

 

[Frank & Pam Hayes]

Alan ... that worked perfectly. Thanks for the help.

Frank

 

[Tom Ogilvy]

Another possibility:

Dim MyArray as Variant
Dim NumRows as Long
Dim NumCols as Long

NumRows = 5
NumCols = 2

MyArray = Sheets(10).Range("A1").Resize(numRows, _
numCols)).Value