Quote Originally Posted by jindon View Post
What's wrong with
Arr = Range("C4:E6").Value
?
Hi jindon…
. If that is the final result that the OP wants then I would usually do exactly what you suggested. It is the common neat way to “capture” a Range into an Array (I think? – I am still learning these things!).

. I was not sure if the OP for some reason wanted an intermediate step with some record of the Indexes.(If that were the case this would be a code along both our lines giving an Array of those indexes:

Sub ArrayEvaluateRowColumn()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")

Dim Arr() As Variant

Let Arr() = evaluate("Row(" & rng.Address & ")" & "&"" , ""&" & "Column(" & rng.Address & ")")


End Sub 'ArrayEvaluateRowColumn()
)

. Possibly if the OP is new to VBA like me he does not quite understand how or why your simple code line works, but similar ones do not. Maybe that is the key to understanding how and why the code that works works! (Any insight there would be very welcome!)

For example, why does this work…
Sub jindonArr()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr() As Variant
Let Arr() = rng.Value
End Sub 'jindonArr()
… but this does not?!?

Sub jindonArr2()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr() As String
Let Arr() = rng.Value
End Sub 'jindonArr2()
.. I would have expected the second code to work as I had strings in the cells - as was confirmed by looking at the array values from the first code in the Watch Window







Alan
P.s. Another possibility would be that the OP wants an Array of Ranges. Again versions of your simple code just in case that is what he wants to do that would be

Sub jindonArrOfRanges()
Dim Arr As Range
Set Arr = Range("C4").CurrentRegion
End Sub 'jindonArrOfRanges()
'
'
'
'
'




Sub jindonArrOfRanges2()
Dim Arr As Range
Set Arr = ThisWorkbook.Worksheets("CrazyRanges").Range("C4").CurrentRegion
End Sub 'jindonArrOfRanges2()
Sub jindonArrOfRanges4()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr As Range
Set Arr = rng.Range("C4:E6")
End Sub 'jindonArrOfRanges4()


Sub jindonArrOfRanges3()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr As Range
Set Arr = rng.CurrentRegion
End Sub 'jindonArrOfRanges3()