Solved: copy range issue

[drawworkhome]

excel is giving me fits with this snippit:

Range("A5:b30,b41:c47,a54:b54").Copy

its ok with two ranges, but when i add the third, it dont like it.
can someone explain why?
it works with Range("A5:b30,a54:b54").Copy

ive looked around but cant find any kb on it.

thanks.
erik

[GTO]

As far as I know, this equates to what you can do manually. You can select for instance, B1:B10 and B15:B20 and click copy. If the two ranges are in different areas however, Excel will balk.

Mark

[p45cal]

As GTO says, it does seem to equate with what you can/can't do manually with selecting artea and then Ctrl+C. If Excel doesn't like it, it reports "That command cannot be used on multiple selections" (xl2007 at least). The only thing is.. it's patently incorrect, you can copy multiple areas. The caveat seems to be that for a successful operation, the areas must all be either:
-all the same columns
or
-all the same rows
or,
-all the same columns AND all the same rows viz:.
2340
Note,than when it comes to the paste operation the result is one single block. Maybe that's the criterion, if it can reduce the selection to a single block it will allow multiple area copy?
Perhaps like copying a single block which has hidden/filtered columns and/or filtered/hidden rows.

[drawworkhome]

any opinions on using union?

[p45cal]

any opinions on using union? I think that because the range addresses are separated by commas (the union operator) and these are in double quotes, the union is already happening.
Even if it's not, I still don't think it'll make any difference.

What, ultimately, are you intending to do with the copied range?

[drawworkhome]

i intend to paste it on the same sheet and then compare some named range info next to it. i have since changed it to



Range("A5:b30, a41:b47, a54:b54").copy


and it works. i think excel doesnt like it when you try and copy three ranges of info from different columns. im sure they had a good reason for it but seems quite limiting.

[lucas]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=352

http://www.vbaexpress.com/kb/getarticle.php?kb_id=354

[drawworkhome]

lucas, i did find those articles and tried parts of them but my 2007 still didnt like it or i did something wrong (not suprising..).
e

[drawworkhome]

i intend to paste it on the same sheet and then compare some named range info next to it. i have since changed it to



Range("A5:b30, a41:b47, a54:b54").copy


and it works. i think excel doesnt like it when you try and copy three ranges of info from different columns. im sure they had a good reason for it but seems quite limiting.
i would like to add that i could select the three ranges, just was not able to copy them.

[lucas]

This seems to work as far as letting you copy and paste but it removes any spaces between rows......

Sub a()
Range("A5:b30, a41:b47, a54:b54").Copy
Range("g5").PasteSpecial
End Sub

[drawworkhome]

lucas, thank you and your book1 is what i am doing in my workbook, like the yellow color too..:whistle:

[drawworkhome]

does anybody know why the blank cells are removed?

[lucas]

They are not removed, they are just never copied to begin with.

You can break this up as shown in the attachment.

[GTO]

Hi Steve :-)

You know, if you don't chunk the furniture on your car's roof and head out pretty soon, it may be reversed! Ya'll may see a dually CC wandering through the neighborhood aimlessly till ya wave me down :-)

Rainy here today, and I'm on Shift III for the time being. If I could excuse myself, I think I'd cry...


excel is giving me fits with this snippit:

Range("A5:b30,b41:c47,a54:b54").Copy

...


does anybody know why the blank cells are removed?

Hi Erik,

Maybe of no assistance at all, but I was thinking on the original problem; that being of copying different areas (much better described by Pascal). Given that you also asked about the 'in-between' rows/columns not showing, it got me to thinking...

This may be WAY less-than-stellar, but rather than fight excel, maybe copy the entire range and clear the stuff we don't want?


Option Explicit

Sub CopyParts()
Dim rngRaw As Range, rngDisclude As Range

Set rngRaw = Range("A5:C54")
Set rngDisclude = Application.Union(Range("C5:C30"), _
Range("A31:C40"), _
Range("A41:A47"), _
Range("A48:C53"), _
Range("C54"))
rngRaw.Copy Range("G1")

rngDisclude.Offset(-4, 6).Clear
End Sub

If this is just goofy, please don't hesitate to say so.

A great day to all ya'll,

Mark

[lucas]

Hi Mark, Okies these days are poorer than the dustbowl okies and can't even afford to escape.

If you get in my neighborhood you will know me when you get far enough out in the country and my dogs start barking.

Weather's breaking here so the grill is always hot.

Pretty elegant solution using union but they are all just brute force.

Your's would present a maintenance headache for many(me) if they came back to it a year from now....:fright:

[GTO]

...If you get in my neighborhood you will know me when you get far enough out in the country and my dogs start barking.

Weather's breaking here so the grill is always hot...

If you add a horse whinnying or even neighing, I will weep...


...Pretty elegant solution using union but they are all just brute force.

Your's would present a maintenance headache for many(me) if they came back to it a year from now....:fright:

I fully admit that you got well beyond me there.

I do not see getting beyond the OP's original ranges to copy; but as to catching the overall range, would this be better?


Sub CopyParts_2()
Dim rngDisclude As Range, rngRaw As Range, rCell As Range, lCol As Long

Set rngDisclude = Application.Union(Range("C5:C30"), _
Range("A31:C40"), _
Range("A41:A47"), _
Range("A48:C53"), _
Range("C54"))
lCol = Columns.Count
For Each rCell In rngDisclude.Rows
If rCell.Column < lCol Then lCol = rCell.Column
Next

Set rngRaw = Range(Cells(rngDisclude.Row, lCol), rngDisclude.SpecialCells(xlCellTypeLastCell))


rngRaw.Copy Range("G1")

rngDisclude.Offset(-4, 6).Clear
End Sub

Mark