Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
2003, 2007
Would like to be able to ClearContents in the cells representing the difference between Range1 and Range2. If possible like: Range3 = Union(Range2 - Range1) Range3.ClearContents I cannot find a function to accomplish the task. What am I not considering? TIA EagleOne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
Can you expand on "ClearContents in the cells representing the difference"
What do you mean? -- Regards, Nigel wrote in message ... 2003, 2007 Would like to be able to ClearContents in the cells representing the difference between Range1 and Range2. If possible like: Range3 = Union(Range2 - Range1) Range3.ClearContents I cannot find a function to accomplish the task. What am I not considering? TIA EagleOne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
Thanks for your time and knowledge,
I can "compute" RowDifferences and ColumnDifferences i.e., (This example selects the cells in column A on Sheet1 whose contents are different from cell A4) Worksheets("Sheet1").Activate Set r1 = ActiveSheet.Columns("A").ColumnDifferences( _ Comparison:=ActiveSheet.Range("A4")) r1.Select Is there a function (like a reverse of a Union) that will permit the selection of the cells which differ from Range1 vs Range2? "Nigel" wrote: Can you expand on "ClearContents in the cells representing the difference" What do you mean? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
You will have to run a loop thru range2 and find the cells that don't intersect with range1. -- Jim Cone Portland, Oregon USA wrote in message 2003, 2007 Would like to be able to ClearContents in the cells representing the difference between Range1 and Range2. If possible like: Range3 = Union(Range2 - Range1) Range3.ClearContents I cannot find a function to accomplish the task. What am I not considering? TIA EagleOne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
If I understand your question correctly, you want to select the cells in the
Union of Range1 and Range2 that lie outside of their Intersection. If that is correct, something this should work for you... ' Calculate the union of the two ranges ' with their intersection omitted Sub SelectDifference(R1 As Range, R2 As Range) Dim I As Range, C As Range Dim Difference As Range Set I = Intersect(R1, R2) For Each C In Union(R1, R2) If Intersect(C, I) Is Nothing Then If Difference Is Nothing Then Set Difference = C Else Set Difference = Union(C, Difference) End If End If Next Difference.Select End Function -- Rick (MVP - Excel) wrote in message ... 2003, 2007 Would like to be able to ClearContents in the cells representing the difference between Range1 and Range2. If possible like: Range3 = Union(Range2 - Range1) Range3.ClearContents I cannot find a function to accomplish the task. What am I not considering? TIA EagleOne |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
I figured that also, thanks for confirming that
"Jim Cone" wrote: You will have to run a loop thru range2 and find the cells that don't intersect with range1. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to ascertain difference between Range1 and Range2
Thanks for your knowledge and time!
"Rick Rothstein" wrote: If I understand your question correctly, you want to select the cells in the Union of Range1 and Range2 that lie outside of their Intersection. If that is correct, something this should work for you... ' Calculate the union of the two ranges ' with their intersection omitted Sub SelectDifference(R1 As Range, R2 As Range) Dim I As Range, C As Range Dim Difference As Range Set I = Intersect(R1, R2) For Each C In Union(R1, R2) If Intersect(C, I) Is Nothing Then If Difference Is Nothing Then Set Difference = C Else Set Difference = Union(C, Difference) End If End If Next Difference.Select End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ascertain Unique value considering Two columns | Excel Worksheet Functions | |||
???Sumif(Range1,Range1<Range2,Range1)??? | Excel Discussion (Misc queries) | |||
How can you ascertain how many "indents" in an excel cell? | Excel Discussion (Misc queries) | |||
How to ascertain path of file in adjacent directory hierarchy | Excel Programming | |||
VBA code to calculate the difference between rows | Excel Programming |