KeepITcool,

Partial diagnosis: DV is being restored twice in
overlapping areas, at least that's what's occuring in my
testing. Work with something manageable and look at:

If colDV.Count 0 Then
For Each itm In colDV
Debug.Print itm(0).Address, itm(0).Count, _
itm(0).Areas.Count

or compare [count] with [sum areas.count]

This is in "Invert" pre "rSqu" amendment, and similar in
the amendment with vitm(0). I say "partial" because
there's also something else going on, I think all easily
fixed but far too late tonight.

regards,
Peter


Following HAS happened:
due to editing or while testing I apparently
damaged/copied not removed
some dummy validation

Funny you should mention that. I've experienced similar
but ignored. Here's something else:

I have a recorded macro to replace identical DV to a
single area over everything before testing. But
occasionally it errors and I need to run the
line ".Delete" (DV) twice. I've got a feeling similar has
occurred in proper code without my knowing. I'm only
testing with small ranges - visible on the screen,
shouldn't need DoEvents.

If you can re-produce your "HAS", try adding a
second .Delete line before applying any DV.

Regards,
Peter
.

get the the inverse of intersect

For your amusement...

Sub Test()
NotIntersect(Selection, Application.InputBox("", , , , , , ,
8)).Select
End Sub

Function NotIntersect(rng As Range, x As Range) As Range
' copyright 2001-2004 Jim Wilcox
Dim y As Range
On Error Resume Next
If rng.Parent Is x.Parent Then
With x
Set y = myUnion(y, Range(Rows(1), .Rows(0)))
Set y = myUnion(y, Range(Rows(Rows.Count), .Rows(.Rows.Count +
1)))
Set y = Intersect(y, .EntireColumn)
Set y = myUnion(y, Range(Columns(1), .Columns(0)))
Set y = myUnion(y, _
Range(Columns(Columns.Count), .Columns(.Columns.Count + 1)))
Set y = Intersect(y, rng)
End With
Set NotIntersect = y
End If
On Error GoTo 0
End Function

Private Function myUnion(o As Range, rng As Range) As Range
On Error Resume Next
If o Is Nothing Then
Set myUnion = rng
ElseIf rng Is Nothing Then
Set myUnion = o
Else
Set myUnion = Union(o, rng)
End If
On Error GoTo 0
End Function

-Jim (see Organization field to figure out email address)

Hi Jim,

I suspect KeepITcool is doing the sensible thing and gone
on holiday, hence a reply from me.

This looks interesting but I cannot get it to work
correctly, a dot or two out of place perhaps. I had a go
with something similar, didn't pursue when KeepITcool
warned me off the idea (with large ranges).

As time's gone by a quick recap -

To get a pure inverted range, eg
Set RngA = Range("b2:c3,f3:g4")
Set RngB = Range("b2:g4") 'square or outer RngA

Set rInverted = rFunc(RngA, RngB)

Debug.? rInverted.Address
$B$4:$C$4,$D$2:$E$4,$F$2:$G$2

For my, and I think Norman's purposes, to subtract ranges
whose "outer" areas may only partially intersect. Also,
either/both ranges could be single or multiple. The
functions in this thread are set up to subtract Intersect
(RngA,RngB) from Union(RngA,RngB), but easily adapted to
subtract whatever. Eg:

Set RngA = Range("B2:C3,F3:G4")
Set RngB = Range("B3:B5,C3:G3")

Set rSubtracted = rFunc(RngA, RngB)
' subtract Intersect from Union

Debug.? rSubtracted.address
$F$4:$G$4,$B$4:$B$5,$B$2:$C$2,$D$3:$E$3

I would expect your code to be slower than the methods
discussed, but good for smaller ranges to avoid using DV
or CF. As it stands it does not appear to return the non-
intersecting areas of the ranges thrown at it. I'm hoping
I've missed something obvious and looking forward to one
of those Doh moments :)

Regards,
Peter


-----Original Message-----
get the the inverse of intersect

For your amusement...

Sub Test()
NotIntersect(Selection, Application.InputBox
("", , , , , , ,
8)).Select
End Sub

Function NotIntersect(rng As Range, x As Range) As Range
' copyright 2001-2004 Jim Wilcox
Dim y As Range
On Error Resume Next
If rng.Parent Is x.Parent Then
With x
Set y = myUnion(y, Range(Rows(1), .Rows(0)))
Set y = myUnion(y, Range(Rows(Rows.Count), .Rows
(.Rows.Count +
1)))
Set y = Intersect(y, .EntireColumn)
Set y = myUnion(y, Range(Columns(1), .Columns(0)))
Set y = myUnion(y, _
Range(Columns(Columns.Count), .Columns
(.Columns.Count + 1)))
Set y = Intersect(y, rng)
End With
Set NotIntersect = y
End If
On Error GoTo 0
End Function

Private Function myUnion(o As Range, rng As Range) As
Range
On Error Resume Next
If o Is Nothing Then
Set myUnion = rng
ElseIf rng Is Nothing Then
Set myUnion = o
Else
Set myUnion = Union(o, rng)
End If
On Error GoTo 0
End Function

-Jim (see Organization field to figure out email address)
.

This looks interesting but I cannot get it to work
correctly, a dot or two out of place perhaps.

No.

If you can't get it to work let me help. What was the error message and
line?

-Jim

This looks interesting but I cannot get it to work
correctly, a dot or two out of place perhaps.

No.

If you can't get it to work let me help. What was the
error message and
line?

-Jim

Jim,

Not directly a code halting with an error but more a case
of as I also mentioned last post:

As it stands it does not appear to return the non-
intersecting areas of the ranges thrown at it.


With the examples I posted:

Sub test2()
Dim RngA As Range, RngB As Range

Set RngA = Range("b2:c3,f3:g4")
Set RngB = Range("b2:g4") 'square or outer RngA

Set RngA = NotIntersect(RngA, RngB)
RngA.Select
Debug.Print RngA.Address
End Sub

Here, RngA.Select errors because, in your func:
Set y = Intersect(y, rng) is a non intersecting range,
hence the function returns a non existant range.

Or,

Set RngB = Range("B2:C3,F3:G4")
Set RngA = Range("B3:B5,C3:G3")
Set RngA = NotIntersect(RngA, RngB)

RngA.address returns: B4:B5,D3:G3
Instead of: F4:G4,B4:B5,B2:C2,D3:E3 ($'s trimmed)

Have I missed something?

Regards,
Peter

Have I missed something?

Yes.

Function NotIntersect(rng As Range, x As Range) As Range

rng is the source
x is the range to be removed from the source

I presented the function in such a way that x should be a single-area
range, because calling the function within a loop...

for each x in bigx.Areas
NotIntersect(Selection, x).Select

....or whatever, would be a trivial exercise for the reader, and would
detract from understanding the basic and very simple concept of what my
code achieves, extremely efficiently.

-Jim

Hi Jim,

I presented the function in such a way that x should be a single-area
range, because calling the function within a loop...

for each x in bigx.Areas
NotIntersect(Selection, x).Select

...or whatever, would be a trivial exercise

In his initial post in this thread, KeepITcool explicitly excluded the loop
approach:

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

This need is reinforced by the repeated discussion in the thread of the 8192
non-contiguous areas bug in conjunction with comments such as the following
(from KeepITCool):

Done some basic testing but even at a:z60000 with 40% random non
blanks.. returned 48 multiarea ranges(avg 7500 areas/range)in the
collection. 90secs.. (1200k cells..372k areas.. but NO errors !

I suspect that testing your function in similar fashion would highlight the
fundamental problem.

My plagiaristic approach and those of Peter and KeepITcool were all
predicated on a fast non-looping solution.



---
Regards,
Norman

Jim,

Thank you for clarifying, I had been hoisted by a series
of self induced presumptions.

Although you introduced this as "For your amusement..." it
is, as you say, extremely efficient.

However I think not efficient to deploy with a large loop
of areas the way you suggest. Eg to return the non-
intersection of bigx and the single area that perfectly
surrounds it - Invert a multiple range.

With 100 areas the increased time compared with other
methods is of no consequence, with the advantage of
requiring neither DV nor CF. But with say 4,000 areas, in
my testing, about a hundred times longer than the few
seconds required using DV subtraction, or similar with
KeepITcool's CF method. I don't think I've misunderstood
anything this time, but if I have please advise.

Once again thank you for posting the code,
Peter

PS written before seeing Norman's adjacent post


I presented the function in such a way that x should be a
single-area
range, because calling the function within a loop...

for each x in bigx.Areas
NotIntersect(Selection, x).Select

....or whatever, would be a trivial exercise for the
reader, and would
detract from understanding the basic and very simple
concept of what my
code achieves, extremely efficiently.

-Jim
.

Hi, Jim:

I was intrigued by your code. I tried it with the following

Sub Test()
Dim R As Range
Set R = NotIntersect(Range("a2:d5"), Range("b3:c4"))
If R Is Nothing Then
MsgBox ("Nothing!")
Else
MsgBox R.Address
End If

Set R = NotIntersect(Range("b3:c4"), Range("a2:d5"))
If R Is Nothing Then
MsgBox ("Nothing!")
Else
MsgBox R.Address
End If
End Sub

The first call works fine -- it would select all cells in A2:D5 except B3:C4.
The 2nd line SHOULD produce the same result, right? But it doesn't. It returns
Nothing.

Also, if I call it with ranges A2:D5 and B3:D6, in that order, it does not
include cells B6:D6, which are part of the 2nd range but not of the 1st.

The routine would seem to require that the one range is entirely included in
the other, and the larger range must be specified first. Was that your intent?


On Wed, 04 Aug 2004 12:11:37 -0700, "jim.wilcox" wrote:

get the the inverse of intersect

For your amusement...

Sub Test()
NotIntersect(Selection, Application.InputBox("", , , , , , ,
8)).Select
End Sub

Function NotIntersect(rng As Range, x As Range) As Range
' copyright 2001-2004 Jim Wilcox
Dim y As Range
On Error Resume Next
If rng.Parent Is x.Parent Then
With x
Set y = myUnion(y, Range(Rows(1), .Rows(0)))
Set y = myUnion(y, Range(Rows(Rows.Count), .Rows(.Rows.Count +
1)))
Set y = Intersect(y, .EntireColumn)
Set y = myUnion(y, Range(Columns(1), .Columns(0)))
Set y = myUnion(y, _
Range(Columns(Columns.Count), .Columns(.Columns.Count + 1)))
Set y = Intersect(y, rng)
End With
Set NotIntersect = y
End If
On Error GoTo 0
End Function

Private Function myUnion(o As Range, rng As Range) As Range
On Error Resume Next
If o Is Nothing Then
Set myUnion = rng
ElseIf rng Is Nothing Then
Set myUnion = o
Else
Set myUnion = Union(o, rng)
End If
On Error GoTo 0
End Function

-Jim (see Organization field to figure out email address)

Thanks, Peter and Norman.

KeepITcool explicitly excluded the loop
approach:

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

NotIntersect solves 400 areas in less than a second.

repeated discussion in the thread of the 8192
non-contiguous areas bug

Do we have a solution that solves 8000? NotIntersect does, but I do see
that it takes too long -- many minutes. Forgive me, but do we need one?

I don't read this newsgroup. I saw the original post in this thread
during a search for something else, so I haven't read this bug
discussion. FWIW, is it a bug? There are many things that spreadsheets
aren't intended to do, and while it's fun to challenge the boundaries,
perhaps it's also helpful to provide pointers to the more appropriate
application(s).

-Jim

Was that your intent?

Yes.

-Jim (see Organization field to figure out email address)

P.S. I come from the old school of usenet. I mean, for questions like
these, it seems to me that email is more appropriate, no?

Jim,

Do we have a solution that solves 8000?

KeepITcool does for his particular purpose, see
SegmentedCells in one of his posts. I havn't figured how
to adapt this for more generic use. I think Norman was
working on something, privately - ? and a <g

NotIntersect does, but I do see that it takes too long --
many minutes. Forgive me, but do we need one?

Absolutely. For me not often 8000+ but certainly something
fast for x000.

FWIW, is it a bug? [re 8192 limit]

In Excel this may be a sensible design limitation. F5
select 8000+ areas severely strains resources, assuming no
other constraints which there probably are.

But in VBA no error is returned if the limit is exceeded,
incorrectly returns just the first cell. I'd call that a
bug. This contrasts with:
a) in vba, no cells found - generates trappable error
b) Excel only, 8192+ generates Alert "selection too large"

Something simple like count constants and one cell is
returned, how to know if that's correct. Norman
demonstrated the limit could even be 8191 ! and
additionally a solution to trap the problem.

Back to your function, my results in an old system
(reducing outer-bigx with bigx by areas):

Areas NotIntersect DV or CF method
400 1 sec 0.1 s
1000 15 s 0.5 s
4000 800 s 6 s

Self evident. But I would not bet against the possibility
your function could be radically adapted to reduce the
time by a factor of 2 to 10, depending on no. of areas.

Regards,
Peter

P.S. I come from the old school of usenet. I mean, for questions like
these, it seems to me that email is more appropriate, no?

I don't agree. I thought the purpose of these discussions was to help other
users. Seeing only part of the discussion doesn't help them...

PS: My confusion is coming from the the name of your routine and the fact that
I haven't followed this thread.

Given the name, NonIntersect, I expected the routine to take two ranges,
create their union, then remove their intersection.

I see from your comments in another message that you want to take the first
range and remove from it any cells that are also part of the 2nd range. That's
not the problem I expected, but presumably it's what the OP wanted.


On Fri, 06 Aug 2004 16:14:01 -0700, "jim.wilcox" wrote:

Was that your intent?

Yes.

-Jim (see Organization field to figure out email address)

P.S. I come from the old school of usenet. I mean, for questions like
these, it seems to me that email is more appropriate, no?

Re email:

If I display the header fields, the organization field gives your company
name, not your email address at the company. I use Agent as my newsreader.
Perhaps Outlook Express is different.

these, it seems to me that email is more appropriate, no?