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

[kknb0800]

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.

Please Login or Register  to view this content.

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!

[nathansav]

I think you have to put "=" & range("n" & x).text

[kknb0800]

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

[nathansav]

Can you attach the data?????

[AB33]

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

May be,change

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

[kknb0800]

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?

[nathansav]

Google what you've just asked us

[Vikas_Gautam]

Tyre this..

Please Login or Register  to view this content.

[kknb0800]

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

[nathansav]

If you are deleting duplicates, why not just add the range to an array set arr()=range("a1:a1000") then itterate it, putting the values into a dictionary, using the key to detect the dupes, then delete the row.

[Vikas_Gautam]

Here is a tested and complete workaround of what you need..

Please Login or Register  to view this content.

[tonyr63]

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