Consulting

Results 1 to 5 of 5

Thread: vba - select and copy multiple rows in different columns

  1. #1

    vba - select and copy multiple rows in different columns

    Hello,

    I tried 3 different ways of selecting and copying data that are in different rows/columns. They all give me error 400, unless they are in the same columns.

    Sub button1()
    
    
    Application.Union(Range("B14", "B18"), Range("A21", "A24")).Select
    
    Selection.Copy
    
    End Sub
    or

    Sub button1()
    
    
    'Application.Intersect(Range("B14", "B18"), Range("A21", "A24")).Select
    
    Selection.Copy
    End Sub
    or

    Sub button1()
    
    
    Range("B14:B18, A21:A24").Select
    Selection.Copy
    
    
    End Sub
    Is there a way to accomplish what I am trying to do ?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Quote Originally Posted by MasterBash View Post
    I tried 3 different ways of selecting and copying data that are in different rows/columns. They all give me error 400, unless they are in the same columns.
    I get this:

    2024-06-15_180556.jpg

    which is not strictly accurate. If the areas you select can condense to a neat rectangular range (with a few other conditions) you can copy multiple selections at once. This works:
    Range("A14:A18,D14:D18,D21:D23,A21:A23").Copy
    2024-06-15_181104.jpg

    and you can copy those four areas at once to somewhere else (F14 in this case) with
    Range("A14:A18,D14:D18,D21:D23,A21:A23").Copy  Range("F14")
    resulting in:

    2024-06-15_181419.jpg

    Trying to copy the yellow highlighted area below won't work however, even though it can be resolved to a single rectangular range:

    2024-06-15_181624.jpg

    In general, it's better to copy those areas one at a time by looping through them eg.:
    For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas
    You could use that like say:
    Set destn = Range("F32")
    For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas
      are.Copy destn
      Set destn = destn.Offset(are.Rows.Count)
    Next are
    which results in:

    2024-06-15_182054.jpg

    or simply by having one line per area that you want to copy:
    Range("A32:A36").Copy Range("F32")
    Range("A39:A41").Copy Range("G32")
    Range("D32:D35").Copy Range("H32")
    Range("D38:D41").Copy Range("I32")
    which gives you:

    2024-06-15_182913.jpg
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    I am just looking to copy them to the clipboard so I can paste in another program.

    Set destn = Range("F32")
    For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas
      are.Copy destn
      Set destn = destn.Offset(are.Rows.Count)
    Next are
    I believe this is the best option. However, I am worried about the problems this may cause if multiple people uses the worksheet. I believe people will be overwriting each other everytime.

    Thank you for bringing up different options. I will probably have to do my worksheet differently.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Perhaps put links to the values you want to copy in a rectangular range somewhere (it doesn't even have to be on the same sheet) and copy that?:
    (Here I'm showing the formulae rather than any values, and you'd copy E14:E22)

    2024-06-16_111648.jpg
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    That is what I ended up doing on another sheet.

    Thank you !

Posting Permissions

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