Hi KeepItCool,

You can see the conversation to which Tom alludes at :

http://tinyurl.com/5yyl4

Stealing acombination of these ideas, I use the following function which, in
my timings, is significantly faster than loop approaches that i tried.

Function RangeNot(RngA As Range, Optional RngB As Range, _
Optional WS As Worksheet)
' Using Dave Peterson interpretation of Tom Ogilvy's
' scratch sheet
' Adapted to replace the scratchsheet using Dana DeLouis's
' Validation idea
'Adapted as a function

Dim wks As Worksheet

If WS Is Nothing Then Set WS = Activesheet

If RngB Is Nothing Then Set RngB = Activesheet.UsedRange

With Union(RngA, RngB).Validation
.Delete
.Add 0, 1
End With

Intersect(RngA, RngB).Validation.Delete

Set RangeNot = Union(RngA, RngB).SpecialCells(xlCellTypeAllValidation)

End Function

---
Regards,
Norman



"keepITcool" wrote in message
...

Hi..

this one's for the experts/mathematicians amongst us..
(Harlan, you reading this ? :)


does anyone have some routines to invert a (multiarea) selection?
or ...along the same line of thought ..

to get the the inverse of intersect.. (generally that would give a
"LEFT" bucket and a "RIGHT" bucket.

It MUST be fast.. thus a simple loop will never suffice.
unions above 400 areas get dreadfully slow..

My theory (and bit of practice too:)

First get the 'outside range' sized from topleft to bottom right cell
of the multiarea.. that's done. (be carefull of unordered areas.)

Then create an array of same dimensions... and mark off the selected
cells. much faster then checking intersect during a 'normal' loop.

But then..? I need an efficient routine to create a a new range object
from that array... Since you want to avoid just dumping every TRUE in
the array in a union and let excel figure it out..

SO probably I need a 'mazing' algorithm but there I'm stuck for the
moment..and I'm pretty sure there must be some nice routines out there!


anyone?..

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