Creating an Array from a Range using Offset
 

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("A1:(A1(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

Creating an Array from a Range using Offset
 

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

Alan Beban

Frank & Pam Hayes wrote:
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("A1:(A1(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

Creating an Array from a Range using Offset
 

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

Frank

"Alan Beban" wrote in message
...
MyArray = Sheets(10).Range("A1:" & Range("A1").Offset(numRows, _
numCols).Address)

Alan Beban

Frank & Pam Hayes wrote:
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("A1:(A1(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

Creating an Array from a Range using Offset
 

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

--
Regards,
Tom Ogilvy

"Alan Beban" wrote in message
...
MyArray = Sheets(10).Range("A1:" & Range("A1").Offset(numRows, _
numCols).Address)

Alan Beban

Frank & Pam Hayes wrote:
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("A1:(A1(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