Rahul

Maybe it's because you refer to Column B in Sheet A ? [Range("B" & i)]

Why not turn it round and go from sheet B and look for matching entries in
sheet A. You could use COUNTIF to check for a match. It's also better to
work from the last row up so that you don't end up jumping over rows.

Something like:

Sub DeleteDuplicates()
Dim LastRow As Long
Dim i As Long
Sheets("B").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If WorksheetFunction.CountIf(Sheets("A").Range("A:A") , Range("A" & i))
0 Then
Range("A" & i).EntireRow.Delete
End If
Next 'i
End Sub

Regards

Trevor


"rahul_chatterjee" wrote in message
om...
Dear All,

Using MS Excel 2002 I am trying to compare data in column A in sheet A
with column A in Sheet B. For every match I want to delete the entire
row in Sheet B. The code I wrote is as below

i = 2
'Start of loop

Sheets("A").Select
store = Range("B" & i)
Sheets("B").Select
'Columns("A:A").Select --may not be needed

Selection.Find(What:=store, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

On Error GoTo ErrTrap
ActiveCell.EntireRow.Delete
'..some more code
GoTo Finally
Loop

For a case when a cell in SheetA.ColumnA does not have a match in
SheetB.ColumnA and error is generated which gets handled by the
ErrTrap. This works fine for the first time a match is not found. The
second time a match is not found excel gives a run time error -
Run-time error '91':
Object variable or With blck variable not set

Can anyone tell what is wrong with the code. Thank you in advance.

Regards,
Rahul