Erkyy
Erkyy

Reputation: 73

Deleting Duplicate Row. (Type Mismatch error)

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

Answers (2)

MGP
MGP

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

user5412293
user5412293

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

Related Questions