Reconciling Data Ranges

[Alan]

Dear all,

In an Excel spreadsheet, I have 2 sets of data composed of alpha
strings stored in Range1 and Range2. Initially the data in Range2 is
identical to Range1 but over time items are deleted from Range2.

Periodically, I want to update Range1 to reflect the changes that have
occured in Range2, in other words if an data item in Range1 does not
exist in Range2 I want to delete it from Range1.

Each range contains>1000 items. Using VBA what is the fastest,smartest
way to complete the task? - my initial shot at cranking the handle
takes for ever!!

In reality the ranges are on different sheets but let's say that they
are in columns A and C and consist of:

A C
T501 T502
T502 T501
T503 .
.. .
.. .
.. .
.. .
.. .
T1000 T1000

Notice in this simple example that T503 no longer exists in the column
C range and that the data order has also changed. Without sorting any
of the data I want to determine that T503 should now be deleted from
the A range.

My ideal solution would be to read in each item from Range1 in sequence
and check whether it exists in Range2 but it must be quick!

Any Ideas please?

 

[Bernie Deitrick]

Alan,

Here's a version that looks across sheets...

Sub MatchUp()
Dim Range1 As Range
Dim Range2 As Range
Dim myR As Long

With Worksheets("Sheet1")
Set Range1 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

With Worksheets("Sheet2")
Set Range2 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

For myR = Range1.Cells.Count To 1 Step -1
If IsError(Application.Match(Range1.Cells(myR), Range2, False)) Then
Range1.Cells(myR).Delete
End If
Next myR

End Sub

 

[Alan]

Thanks Bernie, your suggestion was very helpful.

Alan,

Here's a version that looks across sheets...

Sub MatchUp()
Dim Range1 As Range
Dim Range2 As Range
Dim myR As Long

With Worksheets("Sheet1")
Set Range1 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

With Worksheets("Sheet2")
Set Range2 = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With

For myR = Range1.Cells.Count To 1 Step -1
If IsError(Application.Match(Range1.Cells(myR), Range2, False)) Then
Range1.Cells(myR).Delete
End If
Next myR

End Sub