I know I can use the Intersect function to return the address where two
ranges intersect. The code below will equal N2:BE2 as this is where the
ranges overlap.

InterectString = Intersect(Range("N2:BE2"), Range("N2:BF2")).Address

Is there a way to return the portion of the range where they do not
intersect (i.e. BF2).

Thanks EM

Hi Excel Monkey,

See the archive thread:

algorithm to INVERT a multiarea selection ?
http://tinyurl.com/5gqu29




---
Regards.
Norman


"ExcelMonkey" wrote in message
...
I know I can use the Intersect function to return the address where two
ranges intersect. The code below will equal N2:BE2 as this is where the
ranges overlap.

InterectString = Intersect(Range("N2:BE2"), Range("N2:BF2")).Address

Is there a way to return the portion of the range where they do not
intersect (i.e. BF2).

Thanks EM

How about:

Sub NoIntersectionFor()

Dim rng1 As Range, rng2 As Range
Dim rngNoIsect As Range, cell As Range
Set rng1 = Range("N2:BE2")
Set rng2 = Range("N2:BF2")

For Each cell In rng1.Cells
If Intersect(cell, rng2) Is Nothing Then
If rngNoIsect Is Nothing Then
Set rngNoIsect = cell
Else
Set rngNoIsect = Union(rngNoIsect, cell)
End If
End If
Next

For Each cell In rng2.Cells
If Intersect(cell, rng1) Is Nothing Then
If rngNoIsect Is Nothing Then
Set rngNoIsect = cell
Else
Set rngNoIsect = Union(rngNoIsect, cell)
End If
End If
Next

If Not rngNoIsect Is Nothing Then
MsgBox "No overlap for: " & rngNoIsect.Address
End If


End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"ExcelMonkey" wrote in message
...
I know I can use the Intersect function to return the address where two
ranges intersect. The code below will equal N2:BE2 as this is where the
ranges overlap.

InterectString = Intersect(Range("N2:BE2"), Range("N2:BF2")).Address

Is there a way to return the portion of the range where they do not
intersect (i.e. BF2).

Thanks EM

Yes this seems to work Tim - Thanks again.

EM

"Tim Zych" wrote:

How about:

Sub NoIntersectionFor()

Dim rng1 As Range, rng2 As Range
Dim rngNoIsect As Range, cell As Range
Set rng1 = Range("N2:BE2")
Set rng2 = Range("N2:BF2")

For Each cell In rng1.Cells
If Intersect(cell, rng2) Is Nothing Then
If rngNoIsect Is Nothing Then
Set rngNoIsect = cell
Else
Set rngNoIsect = Union(rngNoIsect, cell)
End If
End If
Next

For Each cell In rng2.Cells
If Intersect(cell, rng1) Is Nothing Then
If rngNoIsect Is Nothing Then
Set rngNoIsect = cell
Else
Set rngNoIsect = Union(rngNoIsect, cell)
End If
End If
Next

If Not rngNoIsect Is Nothing Then
MsgBox "No overlap for: " & rngNoIsect.Address
End If


End Sub

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"ExcelMonkey" wrote in message
...
I know I can use the Intersect function to return the address where two
ranges intersect. The code below will equal N2:BE2 as this is where the
ranges overlap.

InterectString = Intersect(Range("N2:BE2"), Range("N2:BF2")).Address

Is there a way to return the portion of the range where they do not
intersect (i.e. BF2).

Thanks EM