What is the simplest way to undo a union?

I can expand a union with:

Set rng = UNION(rng,newstuff)

I want a function like:

Set rng=DIVORCE(rng, newstuff)

That will take rng and remove the range in newstuff.

Thanks in advance

--
Gary's Student

GS

There's no easy way, that I know. Here's a brute force method

http://www.dicks-blog.com/archives/2...the-selection/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Gary's Student wrote:
What is the simplest way to undo a union?

I can expand a union with:

Set rng = UNION(rng,newstuff)

I want a function like:

Set rng=DIVORCE(rng, newstuff)

That will take rng and remove the range in newstuff.

Thanks in advance

Function Disunion(rng As Range, NewStuff As Range) As Range
Dim rng1 As Range, cell As Range
For Each cell In rng
If Intersect(cell, NewStuff) Is Nothing Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(cell, rng1)
End If
End If
Next
Set Disunion = rng1
End Function

This would only work if there is no intersection between the two ranges.

--
Regards,
Tom Ogilvy




"Gary's Student" wrote in message
...
What is the simplest way to undo a union?

I can expand a union with:

Set rng = UNION(rng,newstuff)

I want a function like:

Set rng=DIVORCE(rng, newstuff)

That will take rng and remove the range in newstuff.

Thanks in advance

--
Gary's Student

Thank you Tom. Its a neat idea. Re-creating the range cell-by-cell, but
execpting out the part I want to divorce.
--
Gary's Student


"Tom Ogilvy" wrote:

Function Disunion(rng As Range, NewStuff As Range) As Range
Dim rng1 As Range, cell As Range
For Each cell In rng
If Intersect(cell, NewStuff) Is Nothing Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(cell, rng1)
End If
End If
Next
Set Disunion = rng1
End Function

This would only work if there is no intersection between the two ranges.

--
Regards,
Tom Ogilvy




"Gary's Student" wrote in message
...
What is the simplest way to undo a union?

I can expand a union with:

Set rng = UNION(rng,newstuff)

I want a function like:

Set rng=DIVORCE(rng, newstuff)

That will take rng and remove the range in newstuff.

Thanks in advance

--
Gary's Student

Hi,

how about this example (not simple at all...)

http://hp.vector.co.jp/authors/VA016...l/ununion2.txt

--
HTH,

okaizawa


Gary's Student wrote:
What is the simplest way to undo a union?

I can expand a union with:

Set rng = UNION(rng,newstuff)

I want a function like:

Set rng=DIVORCE(rng, newstuff)

That will take rng and remove the range in newstuff.

Thanks in advance