CODEWITHSUNDEEP
excelvba
Gary's Student
Gary's Student

Reputation: 96753

Copying Disjoint Ranges with VBA

I am trying to explain copying disjoint ranges to a friend and have discovered I can't even explain it to myself. We have sample data like:

enter image description here

With super simple sample code like:

Sub KopyTest()
    Dim rng1 As Range, rng2 As Range, rng3 As Range

    Set rng1 = Range("C6:D11,C13:D14,C16:D18")
    Set rng2 = Range("C6:D11,F6:F11")
    Set rng3 = Range("C6:D11,G9")

    rng1.Copy
    rng2.Copy
    rng3.Copy
End Sub

rng1 consists of three disjoint blocks Yellow + Green + Blue
rng2 consists of two disjoint blocks Yellow + Pink
rng3 consists of two disjoint blocks Yellow + single Gray cell

The first two ranges copy just fine. rng3 fails to copy and we get a multiple selections error message.

We don't understand what sets of disjoint ranges can be copied directly, what sets require UNION() and what sets will just fail

Once we understand this, we can cook up some code to analyze a range and determine if it is "copy-able" and not rely on OnError.

Upvotes: 1

Views: 1383

Answers (1)

JNevill
JNevill

Reputation: 50034

I believe the answer is here on the microsoft support page for the particular error you are receiving (from XL2000). It's as zany as you may expect it to be:

These error messages may occur when your cell or range selections are in different columns or different rows. You can, however, copy and paste nonadjacent ranges without getting an error message if your selection contains any of the following:

Multiple rows within the same column, for example, A1, A3, A5.

Multiple columns within the same row, for example, A2, C2, E2.

Multiple rows and columns within the same columns and rows, for example, A1, C1, A3, C3, A5, C5.

That last range of yours doesn't fit into any of the three accepted multi-selections.

The allowable multi-select ranges feels very similar on what would be allows in an array formula or sumproduct formula.

In the next section on that help page uses the verbiage "...a contiguous rectangle must remain...". And I think that might be the most succint way of stating what is acceptable in a multi-select copy/cut/paste. In other words, think about what would happen if instead of .Copy you .Cut this Multi-Select range. Does the outcome make sense? Can the remaining range be "slid" together to remove those multi-selected cells? If not, then you can't copy either. For reasons.

Upvotes: 3

Related Questions