Runtime error '1004': Unable to get the countif property of the worksheetFunction class.. Please Help!

[rollapig]

Hi all,

This is my first post, so bear with me.

I am currently populating a whole lot of different/identical data to a worksheet and then using a concatenated string in front of all the different figures I am determining which ones are the same. However I now need to delete the duplicate rows (entire rows). I found this code on the net which was working perfectly as long as I had less than 50 columns of data, anything over and I will get the error:

Runtime error '1004':
Unable to get the countif property of the worksheetFunction class

The problem is that there could be hundreds of columns!

When I click debug, I get this piece of code
(If Application.WorksheetFunction.CountIf(RNG.Columns(1), V) > 1 Then
RNG.Rows(R).EntireRow.Delete) highlighted yellow. Written in red below!

Any help would be greatly appreciated. Would like to fix this code, as it vital for the next process in my code.


'Delete Duplicates of Column B

Sheets("Prices").Select
Range("B:B").Select
Dim R As Long
Dim N As Long
Dim V As Variant
Dim RNG As Range

Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual

Set RNG = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))


Application.StatusBar = "Processing Row: " & Format(RNG.Row, "#,##0")

N = 0
For R = RNG.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = RNG.Cells(R, 1).Value

If V = vbNullString Then
If Application.WorksheetFunction.CountIf(RNG.Columns(1), vbNullString) > 1 Then
RNG.Rows(R).EntireRow.Delete
N = N + 1
End If
Else

If Application.WorksheetFunction.CountIf(RNG.Columns(1), V) > 1 Then
RNG.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R


Thanks

rollapig

 

[doofusboy]

You might be able to try replacing:

If Application.WorksheetFunction.CountIf(RNG.Columns(1), V) > 1 Then

with

If Application.CountIf(RNG.Columns(1), V) > 1 Then

 

[rollapig]

Hi,

Thanks for your reply. I did the change you suggested but now I am getting this error:

Run_time error '13'
Type mismatch

If Application.CountIf(RNG.Columns(1), V) > 1 Then

Any ideas?