Opposite of Intersect function - an example

[DataFreakFromUtah]

No question here, just a procedure for the archive.
Search criteria: reverse intersect mutually exclusive ranges
ranges not shared range not shared interesect opposite opposite of
intersect
intersect reverse mutually exclusive range
evalute intersect select opposite intersects
indentify intersect opposite

a modification of some code that Jim Rech, Excel MVP posted on August
25, 2004:

Sub IntersectOppositeExample()

'Example of how to select the mutually exclusive
'portion(s) of two ranges that intersect in Excel
Dim Rng1, Rng2, AllRange, ExcludeRange As Range
Dim NewRg, CurrCell, AntiRange As Range

On Error Resume Next
Set Rng1 = Application.InputBox( _
prompt:="Select 1st Range of Cells
to Evaluate", Type:=8)
Set Rng2 = Application.InputBox( _
prompt:="Select 2nd Range of Cells
to Evaluate", Type:=8)
Set AllRange = Union(Rng1, Rng2)
Set ExcludeRange = Intersect(Rng1, Rng2)


For Each CurrCell In AllRange.Cells
If Intersect(CurrCell, ExcludeRange) Is Nothing Then
If NewRg Is Nothing Then
Set NewRg = CurrCell
Else
Set NewRg = Union(NewRg, CurrCell)
End If
End If
Next
Set AntiRange = NewRg
AntiRange.Select
End Sub

 

[DataFreakFromUtah]

Here's a couple more Intersect exclude range examples:


Sub IntersectRangeExcludeFromRange1Example()
'Example of how to exclude the intersect of two
'ranges(Range 1 & Range 2) from Range 1.
Dim Rng1, Rng2, ExcludeRange As Range
Dim NewRg, CurrCell, AntiRange As Range

On Error Resume Next
Set Rng1 = Application.InputBox( _
prompt:="Select 1st Range of Cells
to Evaluate", Type:=8)
Set Rng2 = Application.InputBox( _
prompt:="Select 2nd Range of Cells
to Evaluate", Type:=8)

Set ExcludeRange = Intersect(Rng1, Rng2)


For Each CurrCell In Rng1.Cells
If Intersect(CurrCell, ExcludeRange) Is Nothing Then
If NewRg Is Nothing Then
Set NewRg = CurrCell
Else
Set NewRg = Union(NewRg, CurrCell)
End If
End If
Next
Set AntiRange = NewRg
AntiRange.Select
End Sub




Sub IntersectRangeExcludeFromRange2Example()
'Example of how to exclude the intersect of two
'ranges(Range 1 & Range 2) from Range 2.
Dim Rng1, Rng2, ExcludeRange As Range
Dim NewRg, CurrCell, AntiRange As Range

On Error Resume Next
Set Rng1 = Application.InputBox( _
prompt:="Select 1st Range of Cells
to Evaluate", Type:=8)
Set Rng2 = Application.InputBox( _
prompt:="Select 2nd Range of Cells
to Evaluate", Type:=8)

Set ExcludeRange = Intersect(Rng1, Rng2)


For Each CurrCell In Rng2.Cells
If Intersect(CurrCell, ExcludeRange) Is Nothing Then
If NewRg Is Nothing Then
Set NewRg = CurrCell
Else
Set NewRg = Union(NewRg, CurrCell)
End If
End If
Next
Set AntiRange = NewRg
AntiRange.Select
End Sub