VBA - copy non-contiguous range in loop

[Trebor8484]

Hi all,

Any ideas why the below piece of code won't copy the desired range.

It should be copying ranges C2:K2 and L2:M2 in the first iteration.

Basically the code should always copy C:K for the relevant row in the loop then either L:M,N:O,P:Q,R:S,T:U depending on which column the loop is currently on.

 Sub MergeMe()


    Dim lr As Long
    Dim DataSht As Worksheet


    Dim c As Range
    Dim CopyRange As Range
    Dim dstsht As Worksheet
    
    Set DataSht = Sheets("Data")
    Set dstsht = ThisWorkbook.Sheets("Merged")
    
    lr = DataSht.Range("A" & Rows.Count).End(xlUp).Row
    
    For Each c In DataSht.Range("L2:L" & lr & ",N2:N" & lr & ",P2:P" & lr & ",R2:R" & lr & ",T2:T" & lr)
        If c.Value <> "" Then
            Set CopyRange = Union(DataSht.Range(Cells(c.Row, "C"), Cells(c.Row, "K")), DataSht.Range(Cells(c.Row, c.Column), Cells(c.Row, c.Offset(0, 1))))
            CopyRange.Copy
            dstsht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
    Next c


End Sub

Thanks

 

[Fluff]

You need to qualify the Cells as well as the Range, try

With datasht
   Set copyrange = Union(.Range(.Cells(c.Row, "C"), .Cells(c.Row, "K")), .Range(.Cells(c.Row, c.Column), .Cells(c.Row, c.Offset(0, 1))))
End With

 

[Trebor8484]

Thanks Fluff

Its not erroring now, but the range isn't correct:

?copyrange.Address
$A$2:$L$2

It should be C2:M2

So on the first iteration of the loop the last part of the range function: .Range(.Cells(c.Row, c.Column), .Cells(c.Row, c.Offset(0, 1))))

Should return: L2:M2

?c.Address
$L$2
?c.offset(0,1).Address
$M$2


Thanks

 

[Fluff]

How about

   Set copyrange = Union(.Range("C" & c.Row).Resize(, 9), c.Resize(, 2))

 

[Trebor8484]

So, just to clarify, if the loop later finds something in column N

For Each c In DataSht.Range("L2:L" & lr & ",N2:N" & lr & ",P2:P" & lr & ",R2:R" & lr & ",T2:T" & lr)

Then the range should be C2:K2,N2:O2

Then

 

[Fluff]

Have you tried the code in post#4?

 

[Trebor8484]

Hi, yes just tested and it works perfectly thanks.

Just out of interest, why the need for a with statement to set the range? I would have thought that as the For Each Loop had set the sheet then the rest of the loop would have known what sheet it referred to?

 

[Fluff]

If you are working on the non-active sheet you need to qualify all ranges & cells, otherwise VBA will assume that they refer to the active sheet.
Although with the slimmed down code in post#4 there is no real need for the with statement anymore, so you could just use

   Set copyrange = Union(datasht.Range("C" & c.Row).Resize(, 9), c.Resize(, 2))

 

[Trebor8484]

Sorry, another quick question.

I also need to copy whatever is in column Z for the particular row number in the loop. Not sure how to amend the resize function to accommodate?

 

[Fluff]

How about

         Set CopyRange = Union(.Range("C" & c.Row).Resize(, 9), c.Resize(, 2), .Range("Z" & c.Row))