CODEWITHSUNDEEP
vbaexcel
DaZn
DaZn

Reputation: 127

Compare two columns and clear contents if duplicates

I want to compare columns A and B and clear the duplicates in column B.

Sub deleteDoublicates()
Dim i As Long
With Sheets("Tabelle1")
    For i = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Application.IsNumber(Application.Match(.Cells(i, "A"), Sheets("Tabelle1").Columns(1), 0)) Then
            .Cells(i, "B").ClearContents

        End If
    Next
End With
End Sub

There must be something completely wrong because it deletes all.

Upvotes: 1

Views: 108

Answers (1)

user4039065
user4039065

Reputation:

You can catch errors from application.match with a variant.

Sub deleteDoublicates()
    Dim i As Long, m as variant
    With Sheets("Tabelle1")
        For i = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
            m = Application.Match(.Cells(i, "A"), Sheets("Tabelle1").Columns(1), 0)
            if not iserror(m) then
                .Cells(m, "B").ClearContents
            End If
        Next i
    End With
End Sub

Your own code was iterating i through the rows in column A but inexplicably using that row number to clear column B.

Upvotes: 1

Related Questions