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

compare two worksheets and delete rows
 

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


" 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

compare two worksheets and delete rows
 

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

compare two worksheets and delete rows
 

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?

compare two worksheets and delete rows
 

Not to usurp any credit from Tom, nor to "show him up", but this ma
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 sheet

--
bgeie
-----------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...fo&userid=1282
View this thread: http://www.excelforum.com/showthread.php?threadid=54599

compare two worksheets and delete rows
 

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



wrote in message
oups.com...
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?

compare two worksheets and delete rows
 

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" wrote in
message ...

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