Delete Duplicate Values

[jollywood]

Hi all, this is my first post here, let's make it a good one!

I have data in a column that I wish to check for duplicates. If I find a duplicate value in that column, I want to delete the entire row and leave only one instance of the datum.

Now, I found the following code and it works just fine...for numbers up to 15 characters:

Sub DeleteDups()

Dim x As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub


My problem is that some of my numbers will be up to 20 characters in length. Currently, I use this formula:
=COUNTIF($o:$o,o2&"*")-COUNTIF($o:$o,o2&"?*")
Then, I autofill down an adjacent column and then manually delete the duplicates.


I want the vba code that will properly delete all but one instance and will account for values over 15 digits in length. Thanks in advance!

 

[mikerickson]

Use a different test

For x = LastRow To 1 Step -1
    If Application.Match(Cells(x,1), Columns(1), 0) <> x Then Rows(x).Delete
Next x

 

[jollywood]

When I use that, it deletes nearly everything in the worksheet. Even the ones that aren't duplicates.

**Edit: It works perfectly, thank you! The data I'm testing was in column O, not A, lol. DUH!