Hi,

I'm working on an Excel 2000 application. It could be very useful if I could
use something like MINUS operator for Range objects.

Ex. I can do INTERSECT to find a common Range. I can use UNION to find a sum
of Ranges. But can I find a part of a Range that is not included in another
Range object?

I'm surprized but I'm unable to find something like this.

Any help apprectiated

Zbig

Here is a function that works okay from VBA returning a range object

'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zbigniew Malinowski" wrote in message
...
Hi,

I'm working on an Excel 2000 application. It could be very useful if I
could
use something like MINUS operator for Range objects.

Ex. I can do INTERSECT to find a common Range. I can use UNION to find a
sum
of Ranges. But can I find a part of a Range that is not included in
another
Range object?

I'm surprized but I'm unable to find something like this.

Any help apprectiated

Zbig

What exactly your procedure does? It deletes all the cells in a Range?

It's really not what I am looking for...

U¿ytkownik "Bob Phillips" napisa³ w
wiadomo¶ci ...
Here is a function that works okay from VBA returning a range object

'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zbigniew Malinowski" wrote in message
...
Hi,

I'm working on an Excel 2000 application. It could be very useful if I
could
use something like MINUS operator for Range objects.

Ex. I can do INTERSECT to find a common Range. I can use UNION to find a
sum
of Ranges. But can I find a part of a Range that is not included in
another
Range object?

I'm surprized but I'm unable to find something like this.

Any help apprectiated

Zbig

Zbig,

you might also be interested in


osoft.com%3E

url should be on 1 line..
if it fails search google groups for
Message-ID:
Subject: algorithm to INVERT a multiarea selection

note it's part of a 55article thread.

the functions is not perfect and gives problems on COMPLEX multiareas
with 8000+ areas


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


Zbigniew Malinowski wrote :

Hi,

I'm working on an Excel 2000 application. It could be very useful if
I could use something like MINUS operator for Range objects.

Ex. I can do INTERSECT to find a common Range. I can use UNION to
find a sum of Ranges. But can I find a part of a Range that is not
included in another Range object?

I'm surprized but I'm unable to find something like this.

Any help apprectiated

Zbig

It saves them, deletes them to find the non-intersect, and then restores.

Try it before saying it isn't what you want.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zbigniew Malinowski" wrote in message
...
What exactly your procedure does? It deletes all the cells in a Range?

It's really not what I am looking for...

U¿ytkownik "Bob Phillips" napisa³ w
wiadomo¶ci ...
Here is a function that works okay from VBA returning a range object

'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zbigniew Malinowski" wrote in message
...
Hi,

I'm working on an Excel 2000 application. It could be very useful if I
could
use something like MINUS operator for Range objects.

Ex. I can do INTERSECT to find a common Range. I can use UNION to find
a
sum
of Ranges. But can I find a part of a Range that is not included in
another
Range object?

I'm surprized but I'm unable to find something like this.

Any help apprectiated

Zbig

WOW!

A piece of work! I could do an inversion and intersect it to achieve the
same result.

I'm really surprized that MINUS operator is not available directly in Excel
:-(

In my case I found the other way to do what I want but the code will be
useful in other cases.

Thanks

Zbig


Uzytkownik "keepITcool" napisal w wiadomosci
ft.com...
Zbig,

you might also be interested in


osoft.com%3E

url should be on 1 line..
if it fails search google groups for
Message-ID:
Subject: algorithm to INVERT a multiarea selection

note it's part of a 55article thread.

the functions is not perfect and gives problems on COMPLEX multiareas
with 8000+ areas

Hi, Bob:

I have a question:

SetRange is the range from which we want to remove those cells that are also
part of UsedRange. If UsedRange is a complete subset of SetRange, the function
works flawlessly.

But what if the two ranges overlap only partially or not at all? You are
destroying the content of the entire UsedRange and restoring only that part
that overlaps SetRange.

Example:
SetRange is A1:C100
UsedRange is B10:D53

The address of the antiunion is OK, but D10:D53, which doesn't overlap
SetRange, now has the value of 0.

What about setting a new range equal to the *intersect* of SetRange and
UsedRange. If it's not null, set just that intersect to 0, as below.

Of course we are still stuck with the problem that the function can't be
called from a formula in a worksheet cell, but I don't see any solution to
that other than brute-force, and there you could hit a limit on the number of
areas in a multiple selection.

Function AntiUnion2(SetRange As Range, UsedRange As Range) As Range
Dim SaveSet As Variant
Dim Overlap As Range

Set Overlap = Application.Intersect(SetRange, UsedRange)

If Overlap Is Nothing Then
Set AntiUnion2 = SetRange
Else
SaveSet = SetRange.Formula
SetRange.ClearContents
Overlap.Value = 0
Set AntiUnion2 = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange.Value = SaveSet
End If
End Function


On Sun, 21 Nov 2004 11:11:53 -0000, "Bob Phillips"
wrote:

Here is a function that works okay from VBA returning a range object

'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function