Noncontiguous range with Range(Cells(r,c),Cells(r2,c2),Cells(r3,c3)...) ?

[JYTS]

An analogy:

Range("A1:E1") = Range(Cells(1,1),Cells(1,5))
as
Range("A1:C1,E1:G1,I1:L1") = ???

In other words, how/can I refer to noncontiguous ranges with Range(Cells(variable,variable),Cells(var,var),Cells(var,var)...)? Thanks!

[simcode]

maybe
union(range(cells(1,1),cells(1,3)),range(cells(1,"e"),cells(1,"g")),range(cells(1,"I"),cells(1,"L")))

[gbeats101]

Hi

You need to know that its quite a pain working with non contiguous blocks of data take a look below......

Capture.PNG


You cannot directly directly access each region, you have to go through a very undocumented part of excel called 'Areas'

a Range is made of non contiguous Areas (not cells), each Area is a block of contiguous cells (or at least its better to think that way)

the quickest way to put all these together into a single easy to use range is to paste the range into a new sheet which will put all the data together into an easy to use range, otherwise you need to track the areas which is a pain in the backside.........

but Union like it was mentioned before is the easiest way to join with multiple ranges

Paul S.

[mikerickson]

Union is a robust way to build discontinuous ranges.
I agree that they have to be used with care.
I disagree about documentation. The Area object is well documented, its simply a range object.

Another way to get a discourteous range is

Please Login or Register  to view this content.

[JYTS]

Great, thanks for the ideas everyone!