Deleting Duplicate Row. (Type Mismatch error)

Question

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.

what exactly do you want to check for? you want to check if LRow is the last row and what is the second validation? You want to see if the content of the last row is empty? — MGP
@MarcoGetrost The first condition is for the code to check all rows in excel and avoid the empty row to be deleted then delete the duplicate data. — Erkyy

MGP MGP · Accepted Answer · 2015-11-26T07:52:21

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

Deleting Duplicate Row. (Type Mismatch error)

2 Answers