CODEWITHSUNDEEP
excelvba
Alex
Alex

Reputation: 535

Copy-Paste non-contiguous ranges

I have data in Excel. I want to copy the header and some data from the middle of sheet to Powerpoint. I know that you can't copy a selection of non-adjacent cells in Excel, but I was under the impression it would work with VBA.

My try:

With Workbooks(1).Sheets(1)
    Set rng = Union(.Range("B2:K3"), .Range("B45:K85"))
End With

I can select "rng", but I can't paste it anywhere because I get the error message that you can't paste non-adjacent cells.

I've also tried this, but it resulted in the whole table (B2:K85) getting copied:

With ThisWorkbook.Sheets("Sheet1")
        Set rng1 = .Range("B2:K3")
        Set rng2 = .Range("B45:K85")
        Set NewRng = .Range(rng1.Address & ":" & rng2.Address)
End With

I've googled this question and tried various things, but either I misunderstood what is possible with VBA or I'm making a mistake (over and over again).

So do I have to alter my code or do I have to work around it? My alternative solution would be to copy-paste each of the two ranges, put them underneath each other and then copy the whole, now contiguous range.

Upvotes: 4

Views: 6414

Answers (1)

basodre
basodre

Reputation: 5770

You can use the Areas property of the Range object to get the unionized ranges. Code like the below will loop through each of the sub-ranges, copy them, and paste them elsewhere. Try and adapt to your needs, and write back if you need some help.

Sub Test()
    Dim rng As Range
    Dim r As Range
    Dim destination As Range


    Set rng = Union(Range("A1:B3"), Range("D1:E2"))
    Set destination = Range("H1")


    For Each r In rng.Areas
        r.Copy destination
        Set destination = destination.Offset(, 3)
    Next r
End Sub

Upvotes: 4

Related Questions