assign a range of cells to an array

[kevin_floyd]

Is there a way to assign a range of cells to an array in one line of code? I can do it in a for loop, but I am looking for something quicker and easier.

 

[Mudface]

Yes, like this-

Sub AssignToArray()
Dim var() As Variant

var() = Range("A1:A10") ' Read it in

Range("B1:B10") = var() ' Write it back

End Sub

 

[kevin_floyd]

is there any way to cycle through the variant array once it is loaded? I get a subscript error when i try to pull var(i) in a for i = 1 to 10 loop.

 

[Mudface]

You get a 2-D array, so you need something like this-

Sub AssignToArray()
Dim var() As Variant
Dim i As Long

var() = Range("A1:A10") ' Read it in

For i = LBound(var()) To UBound(var())
    MsgBox var(i, 1)
Next i

End Sub

Note that if you have read in more than more than one column, you can use code similiar to this: -

Sub AssignToArray()
Dim var() As Variant
Dim i As Long
Dim x As Long

var() = Range("A1:C5") ' Read it in

For i = LBound(var(), 1) To UBound(var(), 1)
    For x = LBound(var(), 2) To UBound(var(), 2)
        MsgBox var(i, x) ' Reads across then down
    Next x
Next i

End Sub