Dont intersect
 

if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an existing
range?

Dont intersect
 

There is no built in function that does this.

one way would be to loop through all the cells and build a union of cells
not in the intersection.

If you want to use a scratch sheet
Union(rngA,rngb).Formula = "1"
InterSect(rngA,rngB).ClearContents
set rngNot = Union(rngA,rngB).SpecialCells(xlconstants,xlNumber s)

rngNot.ClearContents

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an existing
range?

Dont intersect
 

some cells already have data that i need to keep - just wondered whether it
was doable on range basis - thanx anyway - i'll stop trying now :o)


"Tom Ogilvy" wrote in message
...
There is no built in function that does this.

one way would be to loop through all the cells and build a union of cells
not in the intersection.

If you want to use a scratch sheet
Union(rngA,rngb).Formula = "1"
InterSect(rngA,rngB).ClearContents
set rngNot = Union(rngA,rngB).SpecialCells(xlconstants,xlNumber s)

rngNot.ClearContents

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an existing
range?

Dont intersect
 

I said on a scatch sheet - you would use the resulting address.

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
some cells already have data that i need to keep - just wondered whether
it
was doable on range basis - thanx anyway - i'll stop trying now :o)


"Tom Ogilvy" wrote in message
...
There is no built in function that does this.

one way would be to loop through all the cells and build a union of
cells
not in the intersection.

If you want to use a scratch sheet
Union(rngA,rngb).Formula = "1"
InterSect(rngA,rngB).ClearContents
set rngNot = Union(rngA,rngB).SpecialCells(xlconstants,xlNumber s)

rngNot.ClearContents

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an existing
range?

Dont intersect
 

Tom's suggestion was to use another temporary worksheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim tempWks As Worksheet
Dim rngA As Range
Dim rngB As Range
Dim rngNot As Range

Set wks = ActiveSheet
Set tempWks = Worksheets.Add

With wks
Set rngA = .Range("a1:c9")
Set rngB = .Range("b3:f7")
End With

With tempWks
Union(.Range(rngA.Address), .Range(rngB.Address)).Formula = "1"
Intersect(.Range(rngA.Address), .Range(rngB.Address)).ClearContents
Set rngNot = Nothing
On Error Resume Next
Set rngNot = Union(.Range(rngA.Address), .Range(rngB.Address)) _
.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
End With

If rngNot Is Nothing Then
'do nothing
Else
wks.Range(rngNot.Address).ClearContents
End If

Application.DisplayAlerts = False
tempWks.Delete
Application.DisplayAlerts = True

End Sub

(I thought that it was a pretty neat idea when I saw Tom use it in an earlier
post.)

"JethroUK©" wrote:

some cells already have data that i need to keep - just wondered whether it
was doable on range basis - thanx anyway - i'll stop trying now :o)

"Tom Ogilvy" wrote in message
...
There is no built in function that does this.

one way would be to loop through all the cells and build a union of cells
not in the intersection.

If you want to use a scratch sheet
Union(rngA,rngb).Formula = "1"
InterSect(rngA,rngB).ClearContents
set rngNot = Union(rngA,rngB).SpecialCells(xlconstants,xlNumber s)

rngNot.ClearContents

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an existing
range?





--

Dave Peterson

Dont intersect
 

Don't know if this would be of interest. If one is not using Validation,
one may be able to get away with this non-looping solution. In another Math
program, it is called the "Complement," so I use the same term here. If you
remove the two groups of rows, you should have 6 separate areas that do not
intersect. You can get even fancier if you want to use the
"xlCellTypeSameValidation" variable.


Sub Demo()
Complement [A1:C20,E1:G20], [3:5,9:12]
End Sub

Sub Complement(rng1 As Range, rng2 As Range)
'// Dana DeLouis

With rng1.Validation
.Delete
.Add 0, 1
End With

rng2.Validation.Delete
rng1.SpecialCells(xlCellTypeAllValidation).Select
End Sub

HTH.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dave Peterson" wrote in message
...
Tom's suggestion was to use another temporary worksheet.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim tempWks As Worksheet
Dim rngA As Range
Dim rngB As Range
Dim rngNot As Range

Set wks = ActiveSheet
Set tempWks = Worksheets.Add

With wks
Set rngA = .Range("a1:c9")
Set rngB = .Range("b3:f7")
End With

With tempWks
Union(.Range(rngA.Address), .Range(rngB.Address)).Formula = "1"
Intersect(.Range(rngA.Address),
..Range(rngB.Address)).ClearContents
Set rngNot = Nothing
On Error Resume Next
Set rngNot = Union(.Range(rngA.Address), .Range(rngB.Address)) _
.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
End With

If rngNot Is Nothing Then
'do nothing
Else
wks.Range(rngNot.Address).ClearContents
End If

Application.DisplayAlerts = False
tempWks.Delete
Application.DisplayAlerts = True

End Sub

(I thought that it was a pretty neat idea when I saw Tom use it in an
earlier
post.)

"JethroUK©" wrote:

some cells already have data that i need to keep - just wondered whether
it
was doable on range basis - thanx anyway - i'll stop trying now :o)

"Tom Ogilvy" wrote in message
...
There is no built in function that does this.

one way would be to loop through all the cells and build a union of
cells
not in the intersection.

If you want to use a scratch sheet
Union(rngA,rngb).Formula = "1"
InterSect(rngA,rngB).ClearContents
set rngNot = Union(rngA,rngB).SpecialCells(xlconstants,xlNumber s)

rngNot.ClearContents

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an
existing
range?

Dont intersect
 

Neat!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
There is no built in function that does this.

one way would be to loop through all the cells and build a union of cells
not in the intersection.

If you want to use a scratch sheet
Union(rngA,rngb).Formula = "1"
InterSect(rngA,rngB).ClearContents
set rngNot = Union(rngA,rngB).SpecialCells(xlconstants,xlNumber s)

rngNot.ClearContents

--
Regards,
Tom Ogilvy

JethroUK© wrote in message
...
if:
application.intersect(rnga,rngb).select

selects the range where both ranges intersect

how do i select (identify) the range where they DONT intersect?

or is there a method of UNunion? - i.e remove a range from an existing
range?