I want to create a range (Rng3) that combines the first row and the third row of the table below.
1 2 3 4 5
11 22 33 44 55
111 222 333 444 555
To do this, I use this script:
Sub combineRange()
Dim Rng1, Rng2, Rng3 As Range
Set Rng1 = ActiveSheet.Range("A1:E1")
Set Rng2 = ActiveSheet.Range("A3:E3")
Set Rng3 = Union(Rng1, Rng2)
End Sub
Now, when I inspect Rng3 I see that only the first row of the data is in this range (1,2,3,4,5) and not the third row of the table.
What am I doing wrong?
If the ranges are adjacent, then it does work!
Sub combineRange()
Dim Rng1, Rng2, Rng3 As Range
Set Rng1 = ActiveSheet.Range("A1:E1")
Set Rng2 = ActiveSheet.Range("A2:E2")
Set Rng3 = Union(Rng1, Rng2)
End Sub
Succeeded by defining the range as a collection and by using a function that transforms the collection to an array. See code below:
Sub combineRange()
Dim CombinedRange As Collection
Set CombinedRange = New Collection
CombinedRange.Add ActiveSheet.Range("A1:E1")
CombinedRange.Add ActiveSheet.Range("A3:E3")
'transfer cominedRange to array using function CollectionToArray
varTable = CollectionToArray(CombinedRange)
End Sub
Function CollectionToArray(col As Collection) As Variant()
Dim arr() As Variant, index As Long, it As Variant
ReDim arr(col.Count - 1) As Variant
For Each it In col
arr(index) = it
index = index + 1
Next it
CollectionToArray = arr
End Function