Delete row if 2 cells match in excel [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi,

I tried to use this in as a Excel macro and it removes the wrong data from sheet (Org). Can someone tell me what is wrong with it?

Column D in Sheet "Org" is compared/matched to Column C in Sheet "oldna". If a value in Org matches a value in oldna it should delete the row in Org. Right now it deletes the row before the one that needs to be deleted. I have tried taking out Step -1 but that doesn't do anything at all.

Thanks,
NK
-----------------------
Sub removeold()
Dim sr1 As Long
Dim sr2 As Long
For sr1 = Sheets("Org").Range("D2").CurrentRegion.Rows.Count To 2 Step -1
For sr2 = 1 To Sheets("oldna").Range("c2").CurrentRegion.Rows.Count
If Sheets("Org").Cells(sr1, 2).Value = Sheets("oldna").Cells(sr2, 1).Value Then
Sheets("Org").Rows(sr1).Delete
Exit For
End If
Next sr2
Next sr1
End Sub

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
Well if it always delete the row above, why not change it to
Sheets("Org").Rows(sr1 + 1).Delete

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!