macro to delete entire row of duplicate dates and times dd/mm/yyyy hh:mm:ss

[bradyj7]

Hi all,

The macro below deletes all duplicate rows by checking Column A for duplicate entries. It works fine for ordinary numbers but it wont work for for times and dates (i.e for data in this format 01/03/2011 07:52:04). All entries in column A are in the format dd/mm/yyyy hh:mm:ss. How to I modify the code to do this. I tried declaring the variable x As date but it doesn't work. Any ideas please?

Sub DeleteDups()

Dim x As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub

 

[syntaxed]

Any particular reason why you're using Range("A" & x).Text instead of simply Range("A" & x) ?

 

[VoG]

Try

Sub DeleteDups()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For x = LastRow To 1 Step -1
    If WorksheetFunction.CountIf(Range("A1:A" & LastRow), Range("A" & x).Value) > 1 Then Rows(i).Delete
Next x
End Sub