Thread: Clear Duplicates

Hey everyone this is my first post on any excel forums. I can usually find a solution just by browsing but not this time. What i'm trying to do sounds simple enough. I have 2 Columns of data, example;

1-Apr 1423
1-Apr 1426
24-Apr 1430
8-May 1454
8-May 1460
15-May 1500
15-Jun 1525
15-Jun 1555
15-Jun 1556

I want to clear (not delete) the cells with a duplicate date in them while keeping the cell with the number next to it. So in this case they would be A2, A5, A8, A9. I've come up with a code that looks pretty logical but it doesn't work. Here's what it looks like.

Code:

Public Sub ClearDupes()

    Dim iRows As Integer
    Dim i As Integer
    Dim lCount As Integer
    
    lCount = 1
    iRows = ActiveSheet.UsedRange.Rows.Count
    iRows = iRows - 1
   Sheets("Temp").Range("A1").Activate
    
Do Until IsEmpty(ActiveCell)
    For i = 1 To iRows
        If ActiveCell Is ActiveCell.Offset(i, 0) Then
            ActiveCell.Offset(i, 0).Clear
            lCount = lCount + 1
            Else:
        End If
    Next i

    ActiveCell.Offset(lCount, 0).Select
    lCount = 1
Loop

End Sub

Nothing much happens when i run this, it goes to the empty cell under the last used cell and doesn't clear anything.

TIA

-Chris

--Edited a few bits of the code.

Welcome to the forum.

Try this:

Code:

Public Sub ClearDupes()
    Dim iRow    As Long

    With Worksheets("Temp")
        For iRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
            With .Cells(iRow, "A")
                If .Value = .Offset(-1).Value Then .ClearContents
            End With
        Next iRow
    End With
End Sub