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