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

 

[Trebor8484]

Thanks Fluff, works perfectly

 

[Fluff]

You're welcome & thanks for the feedback