Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Intersect Line | Charts and Charting in Excel | |||
Intersect operations | Excel Worksheet Functions | |||
Intersect Formula??? | Excel Programming | |||
Intersect Formula VBA Help | Excel Programming | |||
Help with If Not Intersect | Excel Programming |