Error 1004 - "Unable to get the CountIf property of the WorksheetFunction class"

Hi,

I'm using the below code to remove duplicates. It reads the values in column N and if a duplicate is found, it deletes the values in columns A to G for the row in which the duplicate is found.

Sub DeleteDupes()
     
    Dim x               As Long
    Dim LastRow         As Long
    
    
    Sheets("Extract").Select
    
    LastRow = Range("A65536").End(xlUp).Row

    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("N1:N" & x), Range("N" & x).Text) > 1 Then
            Range("A" & x & ":G" & x).Select
            Selection.ClearContents
        End If

    Next x
    
  
End Sub

The issue is that it's working correctly up until a point where is throws the 1004 error - "Unable to get the CountIf property of the WorksheetFunction class"

Any ideas what is causing this? Am I better off using another method?

Thanks!

thanks for the quick reply. Unfortunately I get the same error

both - Just trying different things and I've pin pointed the reason why I get the error. It's down to the cell contents length being greater than 254 characters.

The question now is if it's possible to get around this without having to restrict the length of the string within a cell?

When I run it my test, I did not get an error.

May be,change

Range("N" & x).Text

INTO

Range("N" & x).value

Thanks Vikas but it still errors at the same point as before as the cell string length is greater than 254. This is a limitation of using the countif function

Hi

I have the same problem but I cannot understand how from the information posted how Range("H1:H" & LastRow).Value = Evaluate("index(MATCH(N1:N" & LastRow & ", N1:N" & LastRow & ", 0),)") allows you to change ther CountIf range from column "N" to column "B"?

How does this work so i can apply the logic to my problem.

My code is this:

For Each List1Item In List1
'Check if the value of the List1Item in the range occurs more than once
If Application.WorksheetFunction.CountIf(List1, List1Item.Value) > 1 Then
List1Item.Interior.ColorIndex = 36
Dup = Dup + 1
End If
Next List1Item
If Dup > 0 Then
MsgBox "List 1 had " & Dup / 2 & " duplicates highlighted in yellow!", , "Duplicates Found & Marked in List 1"
End If

List1 is a named range