Reputation: 73
I'm getting type mismatch error on Line "If Not LRow = Range("C65536").End(xlUp).Row = "" Then"
Private Sub DEDUPLICATE_Click()
Application.ScreenUpdating = False
Dim n As Long
Dim LRow As Long
If Not LRow = Range("C65536").End(xlUp).Row = "" Then
LRow = Range("C65536").End(xlUp).Row
For n = LRow To 6 Step -1
If Application.WorksheetFunction.CountIf(Range("C6:C" & n), Range("C" & n).Text) > 1 Then
Range("C" & n).EntireRow.Delete
End If
Next n
End If
This code should delete all duplicate entity excluding the empty rows. Tried to change the data type from Long to Variant but it deletes all rows including the empty ones.
Upvotes: 1
Views: 323
Reputation: 2551
Try this:
Private Sub DEDUPLICATE_Click()
Application.ScreenUpdating = False
Dim n As Long
Dim LRow As Long
LRow = Range("C65536").End(xlUp).Row
For n = LRow To 6 Step -1
If Application.WorksheetFunction.CountIf(Range("C6:C" & n), Range("C" & n).Text) > 1 Then
If Not Range("C" & n).Value = "" Then
Range("C" & n).EntireRow.Delete
End If
End If
Next n
End Sub
Upvotes: 1
Reputation:
Its because the rows count is a numeric value and you are comparing it with a string
Private Sub DEDUPLICATE_Click()
Application.ScreenUpdating = False
Dim n As Long
Dim LRow As Long
If Not LRow = Range("C65536").End(xlUp).Row = 0 Then
LRow = Range("C65536").End(xlUp).Row
For n = LRow To 6 Step -1
If Application.WorksheetFunction.CountIf(Range("C6:C" & n), Range("C" & n).Text) > 1 Then
Range("C" & n).EntireRow.Delete
End If
Next n
End If
End Sub
Thanks
Upvotes: 1