subtracting ranges?

[Claude]

Hi all
I would like to subtract one range from another, i.e. the
opposite of rng3 = Union(rng1, rng2). Is this possible?

 

[Vasant Nanavati]

Function AntiRange(rngInner As Range, rngOuter As Range) As Range
Dim c As Range
If InRange(rngInner, rngOuter) Then
For Each c In rngOuter.Cells
If Intersect(rngInner, c) Is Nothing Then
If AntiRange Is Nothing Then
Set AntiRange = c
Else
Set AntiRange = Union(c, AntiRange)
End If
End If
Next
End If
MsgBox AntiRange.Address
End Function

Function InRange(rngInner As Range, rngOuter As Range) As Boolean
'From John Walkenbach
If rngInner.Parent.Parent.Name = rngOuter.Parent.Parent.Name Then
If rngInner.Parent.Name = rngOuter.Parent.Name Then
If Union(rngInner, rngOuter).Address = rngOuter.Address Then
InRange = True
End If
End If
End If
End Function

Note that the inner range has to be completely within the outer range;
otherwise some modification will be necessary.