Delete Rows Based On Cell Contents

[aapke]

I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows.

You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.

I hope that was clear enough!

I also posted this at the following link: http://www.ozgrid.com/forum/showthread.php?t=76209

Thanks much.
Pooja

[boylejob]

Pooja,

See if this will work for you. It worked for me on the same that you sent, but I would play around with it a little before you do it for real on the 22,000

Sub aaa()

Dim lLastRow As Long, lRow As Long
Dim sOldAPN As String
Dim boDup As Boolean

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
sOldAPN = Cells(lLastRow, 1).Value

For lRow = lLastRow - 1 To 1 Step -1
    If Cells(lRow, 1).Value = sOldAPN Then
        Range("A" & lRow).EntireRow.Delete
        boDup = True
    Else
        If boDup = True Then
            Range("A" & lRow + 1).EntireRow.Delete
            boDup = False
        End If
        sOldAPN = Cells(lRow, 1).Value
    End If
    
Next lRow
    
End Sub

[aapke]

Thanks. I figured out how to do it myself with Access.

[aapke]

Thank you. I'll try your macro as well on the Excel file!