CODEWITHSUNDEEP
excelvba
EBassal
EBassal

Reputation: 107

Union of Ranges not storing the values of all the concerned cells

I have seen multiple threads on similar questions, but didn't find a satisfactory answer.

On the first row of an otherwise blank sheet, I have 1, 2, 3, 4 on the first five cells, with a blank cell in the middle. I want to paste these numbers, contiguously, on the line below.

Sub test()

   Dim originCells As Range
   
   Set originCells = Union(Range(Cells(1, 1), Cells(1, 2)), _
   Range(Cells(1, 4), Cells(1, 5)))

   Range(Cells(2, 1), Cells(2, 4)) = originCells.Value

End Sub

I'm getting 1, 2, #NA, #NA. The Union is functionning properly, as I've tested it with a simple

originCells.Select

So the problem is that the last two values are somehow not stored?

I can't believe there isn't an answer to this simple problem?

Thanks

Upvotes: 0

Views: 39

Answers (1)

Warcupine
Warcupine

Reputation: 4640

As noted by BigBen and Scott Craner non contiguous ranges can't be directly equated. You will need to loop or copy to get the values into another range.

Looping:

Sub testloop()

    Dim originCells As Range
   
    Set originCells = Union(Range(Cells(1, 1), Cells(1, 2)), _
    Range(Cells(1, 4), Cells(1, 5)))
    Debug.Print originCells.Address
    Dim cell As Range
    Dim i As Long
    i = 0
    For Each cell In originCells
        Cells(2, 1).Offset(0, i).Value = cell.Value
        i = i + 1
    Next cell
    
End Sub

Copying:

Sub testcolumncopy()
    Dim originCells As Range
   
    Set originCells = Union(Range(Cells(1, 1), Cells(1, 2)), _
    Range(Cells(1, 4), Cells(1, 5)))
    Debug.Print originCells.Address
    originCells.Copy Cells(3, 1)
End Sub

Sub testrowcopy()
    Dim originCells As Range
   
    Set originCells = Union(Range(Cells(1, 1), Cells(1, 2)), _
    Range(Cells(2, 1), Cells(2, 2)))
    Debug.Print originCells.Address
    originCells.Copy Cells(4, 1)
End Sub

Note however that if both the row and column are offset in the unioned range then copy will error.

Sub badtestcopy()
    Dim originCells As Range
   
    Set originCells = Union(Range(Cells(1, 1), Cells(1, 2)), _
    Range(Cells(2, 5), Cells(2, 6)))
    Debug.Print originCells.Address
    originCells.Copy Cells(4, 1)
End Sub

So the safer bet is to loop unless you know that won't be the case.

Upvotes: 1

Related Questions