Copy multiple ranges with VBA

Question

I am trying to copy multiple ranges using Excel VBA. I understand that in order to select multiple ranges, syntax similar to that below is used:

Range("A1:B4000, F1:F4000").Select

This works fine in selecting the appropriate range. However, the following:

Range("A1:B4000, F1:F4000").Copy

...only copies the A1:B4000 range. This is the first problem I am facing.

Secondly, I would like to dynamically copy the data to the bottom row, which is not necessarily row #4000. If selecting a single range, the syntax is as follows:

Range("A1", Range("B1").End(xlDown)).Copy

The above code successfully copies everything from A1 to the bottom of the B column. I can't find any material on the net explaining how to do this for multiple selections.

What I'm essentially trying to do is copy A1:B(bottom) and F1:F(bottom), but the above two issues are stopping me. I assume this is a syntax issue..?

Can you just do a loop and copy and paste twice (so copy and paste the first range and then copy and paste the second range)? It seems that since you are using VBA, you are adding extra complexity by trying to do it all in one step. — rryanp
– rryanp, Commented Sep 17, 2012 at 18:51
Yep, I could do that - that's my current workaround. I just hate workarounds :P — CaptainProg
– CaptainProg, Commented Sep 17, 2012 at 18:53
I just used your code and it worked fine for me (it copied the three columns and pasted them side by side below). What version of Excel are you on? — rryanp
– rryanp, Commented Sep 17, 2012 at 18:56
Have a look at Range("A1").CurrentRegion. That's the easiest path to dynamic ranges, if your data meets the requirements. — Patrick Honorez
– Patrick Honorez, Commented Sep 17, 2012 at 20:49

Scott Holtzman · Accepted Answer · 2012-09-17 19:12:18Z

4

Use the "Union" method.

Dim range1 as Range, range2 as Range, multipleRangeAs Range    
Set range1 = Sheets("Sheet1").Range("A1:B4000")    
Set range2 = Sheets("Sheet1").Range("F1:F4000")    
Set multipleRange= Union(range1, range2)

Then you can mess around with mutipleRange as per normal.

edited Sep 17, 2012 at 19:12

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

answered Sep 17, 2012 at 18:58

MrB

8188 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Holtzman Over a year ago

I would just adjust the following, since the OP asked for dynamic range names, Set range1 = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row) to get the real last cell in column B. Do the same for column F as well.

CaptainProg · Accepted Answer · 2012-09-25 11:11:00Z

0

@Scott Holtzman provided the solution in a comment:

I would just adjust the following, since the OP asked for dynamic range names, Set range1 = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row) to get the real last cell in column B. Do the same for column F as well

answered Sep 25, 2012 at 11:11

CaptainProg

5,75023 gold badges74 silver badges118 bronze badges

Collectives™ on Stack Overflow

Copy multiple ranges with VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related