Só redefini os ranges, veja se é isto :

Fonte : http://www.cpearson.com/excel/ArraysAndRanges.aspx

Sub Array_Selection()
Dim Arr() As Variant

sRg = Selection

Arr = sRg

Dim R As Long
Dim C As Long

    For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
        For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
            Debug.Print Arr(R, C)
        Next C
    Next R

End Sub