Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA - copy non-contiguous range in loop

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - copy non-contiguous range in loop

    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.

    Code:
     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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    You need to qualify the Cells as well as the Range, try
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    How about
    Code:
       Set copyrange = Union(.Range("C" & c.Row).Resize(, 9), c.Resize(, 2))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Oct 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    Have you tried the code in post#4?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Oct 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    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?

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    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
    Code:
       Set copyrange = Union(datasht.Range("C" & c.Row).Resize(, 9), c.Resize(, 2))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Oct 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    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?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA - copy non-contiguous range in loop

    How about
    Code:
             Set CopyRange = Union(.Range("C" & c.Row).Resize(, 9), c.Resize(, 2), .Range("Z" & c.Row))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •