Watch the following code:

Sub RangeToVariant()

 Dim arr
 Dim brr()
 arr = Range("A1:A100")
 ReDim brr(UBound(arr) - 1, 2) 
 arr(1, 1) = 1000
 For i = 0 To 2 
    For j = 0 To UBound(arr) - 1 
       brr(j, i) = i * 100 + j + arr(j + 1, 1) 
    Next 
 Next
 Range("B1:D100") = brr
End Sub

This (tested with xl2000) gives the following information:

- assigning a range to an undeclared (variant) variable is possible
- the size of the variable is not restricted
- declare the variable if its size ("rows" and "columns") has to be changed (ReDim)
- the way back works as simple as this: Assign the variable to a range. That is it!
- normal VBA arrays can be assigned in the same way by omitting the ()
- in the case of no "Option Base 1", the normal index base of 0 automatically assigns as row 1 then
- the variant variable, as described above, is bi-dimensional
- with using arr = Range(...) you do not need to think about the size of the array
- with using Range(...) = arr, exceeding cells will get #NV. A too small range will truncate the most right and bottom values of arr.
- you can not do other than assign: arr = Range(...) * 2 and vice versa would be a calculation, not an assignment.
- the usage of a Range argument within Sub or Function is possible (as in 5036.htm)

An example: A:D has to be converted into the appearance of F:I:

        A     B     C     D     E     F     G     H     I 
  1  Key1  Val1  Key2  Val2                               
  2  A-100    1  A-101    2        A-100    1             
  3  A-102    2  A-103    3                    A-101    2 
  4  A-103    3  A-104    4        A-102    2             
  5  A-105    4  A-107    4        A-103    3  A-103    3 
  6  A-106    5  A-108    5                    A-104    4 
  7  A-107    6  A-109    6        A-105    4             
  8  A-108    8  A-110    7        A-106    5             
  9  A-110    9  A-111    6        A-107    6  A-107    4 
 10  A-111   10  A-112    6        A-108    8  A-108    5 
 11  A-114   11  A-113    5                    A-109    6 
 12  A-115   12  A-114    6        A-110    9  A-110    7 

See following code which needs about 7 seconds (Pentium 4, 3 GHz, 30.000 entries both in A:B and C:D):

Option Base 1

Sub ShowSameEntriesOn1Line()

Dim b(65536, 4)
a = Range("A:D")
i = 2: j = 2: k = 1: l = 1

Do Until k > 65000 Or l > 65000

  If a(i, 1) = a(j, 3) Then
     k = IIf(k > l, k, l) + 1
     l = k
     For m = 1 To 2
        b(k, m) = a(i, m)
        b(l, m + 2) = a(j, m + 2)
     Next m
     i = i + 1
     j = j + 1
  ElseIf a(i, 1) < a(j, 3) Then
     k = k + 1
     l = l + 1
     For m = 1 To 2
        b(k, m) = a(i, m)
     Next m
     i = i + 1
  ElseIf a(i, 1) > a(j, 3) Then
     k = k + 1
     l = l + 1
     For m = 3 To 4
        b(l, m) = a(j, m)
     Next m
     j = j + 1
  End If

Loop

Range("F:I") = b

End Sub