[SOLVED] Delete entire row if duplicate is found in a specific column

Delete entire row if duplicate is found in a specific column

I want to delete the entire row if a duplicate is found in column AF is found. There are a few different numbers in the column and I am trying to only keep one row associated with each number in that column. I keep getting an error that says, "Unable to get Match property of the WorksheetFunction class"

Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
lastRow = Range("A65000").End(xlUp).Row

For iCntr = 1 To lastRow
If Cells(iCntr, 1) <> "" Then
    matchFoundIndex = Application.WorksheetFunction.Match(Cells(iCntr, 2),Range("AF1:AF" & lastRow), 0)
    If iCntr <> matchFoundIndex Then
       Cells(iCntr, 1).EntireRow.Delete
   End If
End If
Next

Solution

Option Explicit

Sub RemoveDups()

    Const COL = "AF"
    Dim r As Long, lastRow As Long, dict, k, n As Long
    Set dict = CreateObject("Scripting.Dictionary")
    
    Application.ScreenUpdating = True
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, COL).End(xlUp).Row
        For r = lastRow To 1 Step -1
            k = Trim(.Cells(r, COL))
            If Len(k) > 0 Then
               If dict.exists(k) Then
                    .Rows(r).Delete
                    n = n + 1
               Else
                    dict.Add k, r
               End If
            End If
        Next
    End With
    Application.ScreenUpdating = True
    MsgBox lastRow & " rows scanned " & n & " rows deleted", vbInformation

End Sub