If you define the range as 4 separate cells (4 references, separated by
commas, rather than A1:A4), then it has 4 areas.

The following line executed in the immediate window, prints 1:

? union(Range("A1"),Range("A2"),Range("A3"),Range("A 4")).Areas.Count
1

If I change "A4" to "D4", it prints 2.

On Mon, 26 Jul 2004 19:05:28 +0100, "Norman Jones"
wrote:

Hi Peter,

I think that depends how the range is created.
Theoretically, after adding / subtracting, I could end up
with:

MsgBox Range("a1,b2,a2,b1").Areas.Count
'or "C1,B2:C2,A3:B3,B4:C4,C5" '5 areas 8 cells


I think that this is misleading. I think the central issue is the number of
non-contiguous areas.

Also, consider:

? range("A1,A2,A3,A4").Areas.Count
4


---
regards,
Norman



"Peter T" wrote in message
...
Hi KeepITcool

do you mean that
a: the selection is incorrect
or
b: that the 'areas' are only 'jumbled'

I meant b: DV is correctly restored using the Collection,
but in a bunch of jumbled areas, rather than the single
area I had applied over everything before testing. I had
thrown two multiple, partially intersecting ranges at
your "Inverse" function. With same test ranges and
Norman's array of DV, I end up with the original single
area of DV. Somehow I forgot to add explanation in my last
post!

Typically a user is unlikely to start with many areas of
DV on his sheet. But could have (say) a couple of columns
of several thousand rows of identical DV - but that's only
one or two areas. Depending on what one's doing, and I
appreciate this might not be applicable in your scenario,
one could end up correctly restoring DV but as several
"jumbled" areas. Not sure what if any implications this
might have, but I would prefer to avoid.

'============================
re other thread Square():
'============================
Peter.. be careful there..

you CANNOT depend on the SEQUENCE of multiareas.
using entirerow/column definitely slows it down.

Function rRect2(rng As Range) As Range
Set rRect2 = Intersect(rng.EntireColumn, rng.EntireRow)
End Function

I had played with this as a precursor to finding
the "outer" coordinates of a multiple range. Idea was
looping this would be faster than looping all the areas.
But I didn't get very far.


'================================
Re How's this for methodology..
'================================
Significant speed improvement if following change is made
to prevent (slow) reunions when either multia is 4096.

4096, or even perhaps 2048?

At this point I need to say that my vba skills are several
pegs down the ladder from those of yours and Norman's -
I have not yet worked out how to use or implement
your "SegmentedCells" function in context. It looks clever
and useful - I'll get there in the end!

A quickie -

Presuming a selection cannot have more areas than 50%
of cells...

I think that depends how the range is created.
Theoretically, after adding / subtracting, I could end up
with:

MsgBox Range("a1,b2,a2,b1").Areas.Count
'or "C1,B2:C2,A3:B3,B4:C4,C5" '5 areas 8 cells

i've got the feeling we'll be back :)

Quicker than you thought!

Regards,
Peter



-----Original Message-----
Hi Peter,

on the contrary thanks for budding in :)

i'm collecting answers here from multi branches in this
thread...

this is off the cuff.. no testing.

do you mean that
a: the selection is incorrect
or
b: that the 'areas' are only 'jumbled'

this seems due to fact that any union or intersect is
build from the
ACTIVEcell forwards.. and wraps around at the end.. first
selected
cell..alas afaik no easy way to recreate/reorder
a 'hashed' multiarea :(

(hence the threads' title?)

'============================
re other thread Square():
'============================
Peter.. be careful there..

i'had already done some speedtesting.
looping may not look cool.
and all the variables may not look cool either..

you CANNOT depend on the SEQUENCE of multiareas.
using entirerow/column definitely slows it down.

Function rRect2(rng As Range) As Range
Set rRect2 = Intersect(rng.EntireColumn, rng.EntireRow)
End Function


'================================
Re How's this for methodology..
'================================
Significant speed improvement if following change is made
to
prevent (slow) reunions when either multia is 4096.
(disproportionate etc :)


change THIS

Set rngT = colRaw(1)
For r = 2 To colRaw.Count
If rngT.Areas.Count + colRaw(r).Areas.Count 8192
Then
SegmentedCells.Add rngT
Set rngT = colRaw(r)
Else
Set rngT = Union(rngT, colRaw(r))
End If
Next
SegmentedCells.Add rngT

to THIS:
Set rngT = colRaw(1)
For r = 2 To colRaw.Count
If rngT.Areas.Count + colRaw(r).Areas.Count m \ 2
Then
SegmentedCells.Add rngT
Set rngT = colRaw(r)
Else
Set rngT = Union(rngT, colRaw(r))
End If
Next
SegmentedCells.Add rngT


i've got the feeling we'll be back :)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :

Hi KeepITCool

Hope you don't mind my squeezing into your thread.
Really like the CF alternative, gives extra
possibilities.

A couple of things with the DV collection if "Goto
useDV".
Similar as I mentioned to Norman, in xl97 would need to
change:

With itm(0).Validation
.Add itm(1), itm(2), itm(3), itm(4), itm(5)
to
.Add itm(1), Abs(itm(2)), itm(3), itm(4), itm(5)

The DV Collection correctly replaces, but when doing
this:

ActiveCell.SpecialCells(xlCellTypeAllValidation).S elect

I get a perfect jigsaw of areas, rather than the single
area of DV I had originally applied over everything.
Using
the array method, when done I end up with the original
single area of DV.

Regards,
Peter



Function Inverse(rngA As Range, Optional bUsedRange
As
Boolean, _
'code

If colDV.Count 0 Then
For Each itm In colDV
With itm(0).Validation
.Add itm(1), itm(2), itm(3), itm(4), itm(5)
.IgnoreBlank = itm(6)
.InCellDropdown = itm(7)
.ShowError = itm(8)
.ErrorTitle = itm(9)
.ErrorMessage = itm(10)
.ShowInput = itm(11)
.InputTitle = itm(12)
.InputMessage = itm(13)
End With
Next
End If

'code

End Function


.