Dont intersect

[JethroUK©]

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?

 

[Tom Ogilvy]

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,xlNumbers)

rngNot.ClearContents

 

[JethroUK©]

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 )

 

[Tom Ogilvy]

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

 

[Dave Peterson]

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.)

 

[Dana DeLouis]

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.

 

[Tushar Mehta]

Neat!

--
Regards,

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