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