VBA code to ascertain difference between Range1 and Range2

[EagleOne]

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

 

[Nigel]

Can you expand on "ClearContents in the cells representing the difference"

What do you mean?

 

[EagleOne]

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?

 

[Jim Cone]

You will have to run a loop thru range2 and find the cells that don't intersect with range1.
--
Jim Cone
Portland, Oregon USA



<[email protected]>
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

 

[Rick Rothstein]

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

 

[EagleOne]

I figured that also, thanks for confirming that

 

[EagleOne]

Thanks for your knowledge and time!