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

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

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?

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

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

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.

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