Non contiguous ranges

[AD108]

I am trying to manipulate 15 non contiguous ranges. I tried typing them
explicitly and got an error saying something about too many arguments (for
the Range. I assume)

My next approach was to try a named range (which seems to be limited to 8
sections)

I defined two named ranges, one consisting of 7 non contiguous ranges, and
the other consisting of 8. ("rng1" and "rng2")

I then defined a third name, "rng", as =rng1, rng2

I was then able to use different methods, such as clear contents, but when I
tried to copy, I get runtime error 1004 with a message saying "This commmand
cannot be used on multiple selections."

Any help would be appreciated. Thanks

AD108

 

[wisccal]

You could copy each individual cell:

Dim rng As Excel.Range, r As Excel.Range

Set rng = Sheets("myWorksheet").Range("rng1, rng2")

For Each r in rng
r.Copy Sheets("TargetWorksheet").Cells(r.Row, r.Column)
Next r

Let me know if that helps you.

Regards,
Steve

 

[Peter T]

Not sure if your question is about making multiple ranges or copying them.

Making a mutli range using string address is limited to absolute max length
255 or maybe a bit less. Applies both with a vba range and Named range. The
difference with your 15 vs 7 might be due to sheet names and $'s in
appearing in the Names dialog. Safe max number of multi areas using string
is 12 though might be much more with single cell areas and addresses like
"A1:A2" vs "$AA$10001:$AA$10002".

So, providing you keep the address string to well under 255 you might be
able to make your 15 areas, depending on their locations in the sheet.

As you've already done you could make two or more (possibly multi-area)
ranges with string method then use the Union function to make a single
range.

Copy/paste multi-areas is effectively not possible, though depending on the
relative layout of areas and if equal area sizes occasionally it works. If
using code then loop through areas -

For each ra in multi-area-range.Areas
' process ra
Next

Regards,
Peter T

 

[AD108]

Thanks for your reply,

Actualy, the combined cells of the area is 13,500. I tried looping through
each already, and it took a considerable amount of time. Trying to find a
faster method.

I am going to try putting the ranges into arrays, and then manipulating the
arrays.

Maybe I can use the openfile method from the destination workbook to let the
user choose the source workbook.

I was originaly just going to have them open both books, and then copy from
one, then activate the other and paste.

You could copy each individual cell:

Dim rng As Excel.Range, r As Excel.Range

Set rng = Sheets("myWorksheet").Range("rng1, rng2")

For Each r in rng
r.Copy Sheets("TargetWorksheet").Cells(r.Row, r.Column)
Next r

Let me know if that helps you.

Regards,
Steve

 

[AD108]

Hi Peter,

Thanks for the reply,

I didn't realize the effect I could get from the areas property. My loops
that I tried were going through each cell. This is very helpful,

Regards

AD108