Although I prefer the solution suggested by OssieMac, an alternative is to
reference the range directly;

Dim myRng as range
Set myRng = Sheet1.Range("A1:D4")

Note that for either solution, Excel defaults to a 0-based array, e.g.
myRng(0 to 3, 0 to 3). If you prefer to work with 1-based ranges, then at the
top of your module add the line
Option Base 1
(note that this is only effective at the module level, and does not affect
the array declarations in other modules)

HTH,
Keith

"OssieMac" wrote:

See if the following 2 examples explains what you need. Note that you simply
declare a variant and assign the range to the variant. Have included the
Lbound and Ubound functions because sometimes you may not know the actual
number of elements.

Also note that when only assigning a single column in this way to a variant
you must include the first dimension when reading the elements in the array.
(With normal single dimension arrays, the dimension is understood.)

Sub RngToArray()

Dim myArray As Variant
Dim Low1 As Long
Dim Upp1 As Long
Dim Low2 As Long
Dim Upp2 As Long
Dim i As Long
Dim j As Long

myArray = Sheets("Sheet1").Range("A1:D4")

'Lowest element in first dimension
Low1 = LBound(myArray, 1)

'Highest element in first dimension
Upp1 = UBound(myArray, 1)

'Lowest element in second dimension
Low2 = LBound(myArray, 2)

'Highest element in second dimension
Upp2 = UBound(myArray, 2)

For i = Low1 To Upp1
For j = Low2 To Upp2
MsgBox myArray(i, j)
Next j
Next i

End Sub


Sub RngToArray2()

Dim myArray As Variant
Dim Low1 As Long
Dim Upp1 As Long
Dim i As Long

myArray = Sheets("Sheet1").Range("A1:A4")

'Lowest element in first dimension
Low1 = LBound(myArray, 1)

'Highest element in first dimension
Upp1 = UBound(myArray, 1)

'Note because assigned to variant you must
'use the first dimension in the formula
For i = Low1 To Upp1
MsgBox myArray(i, 1)
Next i

'Alternative using 1 as the first element
For i = 1 To Upp1
MsgBox myArray(i, 1)
Next i

End Sub


--
Regards,

OssieMac