compare two worksheets and delete rows

I currently have this existing code that checks to if the value is the
same between col E and P and if true, the row is deleted.

'lastrow = Cells(Rows.Count, 4).End(xlUp).Row
'For i = lastrow To 1 Step -1
' If Cells(i, "E").Value = Cells(i, "P").Value Then
' Rows(i).Delete
'End If
'Next


How can I change this to compare values between col A in worksheet 1
and col A in worksheet 2, and if the value is the same, delete that row
in worksheet 1?

Thank you,
Mike

Dim sh1 as Worksheet, sh2 as Worksheet
Dim i as Long
set sh2 = Worksheets(2)
set sh1 = Worksheets(1)
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then
sh1.Rows(i).Delete
End If
Next

you can replace the 2 and 1 (refers to tab order) with actual worksheet
names such as Sheet3 and Sheet4 for example.

set sh1 = Worksheets("Sheet3")

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> I currently have this existing code that checks to if the value is the
> same between col E and P and if true, the row is deleted.
>
> 'lastrow = Cells(Rows.Count, 4).End(xlUp).Row
> 'For i = lastrow To 1 Step -1
> ' If Cells(i, "E").Value = Cells(i, "P").Value Then
> ' Rows(i).Delete
> 'End If
> 'Next
>
>
> How can I change this to compare values between col A in worksheet 1
> and col A in worksheet 2, and if the value is the same, delete that row
> in worksheet 1?
>
> Thank you,
> Mike
>
>

Thanks Tom, you've always been great on helping out!

Quick questions...the macro runs fine with some small test data. I ran
the macro where sh2 had 547 rows and sh1 had 2800 rows. There was a
dupe on sh1 and sh2. On row 2820 sh1 and on row 547 sh2. However the
macro did not remove the row in sh1.

Any ideas why?

Not to usurp any credit from Tom, nor to "show him up", but this may work.

Replace
If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then

with
If sh1.Cells(i, "A").Value = Cells.Find(sh1.Cells(i, "A").Value, ActiveCell, xlFormulas, xlWhole, xlByColumns, xlPrevious, False, False) Then

This should search the entire sheet2 to find the value on sheet1

Mike, I modelled the code on what you had provided - it only checks for rows
in the same location. It is easily altered however to check for a match
anywhere in column 1 of Sheet2.

Dim sh1 as Worksheet, sh2 as Worksheet
Dim i as Long, rng as Range
Dim lastRow as Long
set sh2 = Worksheets(2)
set sh1 = Worksheets(1)
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
With sh2
set rng2 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
For i = lastrow To 1 Step -1
If Application.Countif(rng2,Sh1.Cells(i,"A").Value)>0 then
sh1.Rows(i).Delete
End If
Next


--
Regards,
Tom Ogilvy



<[email protected]> wrote in message
news:[email protected]...
> Quick questions...the macro runs fine with some small test data. I ran
> the macro where sh2 had 547 rows and sh1 had 2800 rows. There was a
> dupe on sh1 and sh2. On row 2820 sh1 and on row 547 sh2. However the
> macro did not remove the row in sh1.
>
> Any ideas why?
>

bgeier,

> Not to usurp any credit from Tom, nor to "show him up", but this may
> work.

Don't give it a second thought. We are all working to help the user and any
meaningful contribution is most welcome.

In that same vein, here is an implementation of my code using your method
that actually does work. I restricted it to look in only column A of the
second sheet based on what the OP originally posted.


Sub AAEE()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim i As Long
Set sh2 = Worksheets(2)
Set sh1 = Worksheets(1)
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
Set rng = sh2.Columns(1).Find( _
sh1.Cells(i, "A").Value, sh2.Cells(Rows.Count, 1), _
xlFormulas, xlWhole, xlByColumns, _
xlPrevious, False)
If Not rng Is Nothing Then
sh1.Rows(i).Delete
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"bgeier" <[email protected]> wrote in
message news:[email protected]...
>
> Not to usurp any credit from Tom, nor to "show him up", but this may
> work.
>
> Replace
> If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then
>
> with
> If sh1.Cells(i, "A").Value = Cells.Find(sh1.Cells(i, "A").Value,
> ActiveCell, xlFormulas, xlWhole, xlByColumns, xlPrevious, False, False)
> Then
>
> This should search the entire sheet2 to find the value on sheet1
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile:
http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=545996
>