I once again yield to you on efficiency and imagination. I especially like
the test for the intersection being null at the beginning. It appears the
the only time the "brute force" approach is necessary is if either of the
ranges have disjoint cells.
--
Gary''s Student


"Harlan Grove" wrote:

Gary''s Student wrote...
The easiest way to exclude a range is to build another range without it:

Sub range_exclude()
Dim r1 As Range, r2 As Range, rx As Range
Set r1 = Range("A1:Z100")
Set r2 = Range("P10:P24")

For Each r In r1
If Not Intersect(r, r2) Is Nothing Then
Else
If rx Is Nothing Then
Set rx = r
Else
Set rx = Union(rx, r)
End If
End If
Next
rx.Select
End Sub

Brute force again. Given a larger single area range A and a smaller
single area range B entirely contained within A (possibly on one or
more edge of A), the complement of B in A is the union of no more than
4 other single area ranges. Needs more code, but runs much more quickly
on large ranges.


Sub foobar()
rc(Range("A1:Z100"), Range("P10:P24")).Select
End Sub


Function rc(a As Range, b As Range) As Range
Dim t As Range

'if b not contained in a, return Nothing
If Intersect(a, b).Cells.Count < b.Cells.Count Then Exit Function

If a.Column < b.Column Then
Set rc = Range(a.Cells(1, 1), _
a.Cells(a.Rows.Count, b.Column - a.Column))
End If

If a.Row < b.Row Then
Set t = Range(b.Cells(1, 1).Offset(-1, 0), _
a.Cells(1, a.Columns.Count))
If rc Is Nothing Then Set rc = t Else Set rc = Union(rc, t)
End If

If b.Column + b.Columns.Count < a.Column + a.Columns.Count Then
Set t = Range(b.Cells(1, b.Columns.Count).Offset(0, 1), _
a.Cells(a.Cells.Count))
If rc Is Nothing Then Set rc = t Else Set rc = Union(rc, t)
End If

If b.Row + b.Rows.Count < a.Row + a.Rows.Count Then
Set t = Range(a.Cells(a.Rows.Count, b.Column - a.Column + 1), _
b.Cells(b.Cells.Count).Offset(1, 0))
If rc Is Nothing Then Set rc = t Else Set rc = Union(rc, t)
End If

End Function