VBA code to ascertain difference between Range1 and Range2

E

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
 
N

Nigel

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

What do you mean?
 
E

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?
 
J

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top