PDA

View Full Version : Solved: VBA to remove duplicates as per the date



harish1805
03-11-2013, 04:13 AM
Hi,

Please help to remove duplicate records as per the date field. I have attached a example file, were date in that records should be a critaria, if its latest should not be removed. For example for Ids 1232 date 12/12/2012 stays, as its the latest date in it and other duplicates are removed.

Thanks!

SamT
03-11-2013, 10:05 AM
That data is impossible to work with. Some data in col "B" is in MS Date/time format (decimal), and some in Excel Text format (13/03/2012), seemingly as a date in dd/mm/yyyy style.

harish1805
03-18-2013, 10:27 PM
This code worked for me!


Option Explicit Sub del_dates() Dim i As Long, lrow As Long Application.ScreenUpdating = False With Worksheets("Sheet1") lrow = .Range("A" & .Rows.Count).End(xlUp).Row For i = lrow To 2 Step -1 If .Range("A" & i).Value = .Range("A" & i - 1).Value Then If .Range("B" & i).Value > .Range("B" & i - 1).Value Then .Rows(i - 1).Delete ElseIf .Range("B" & i).Value < .Range("B" & i - 1).Value Then .Rows(i).Delete End If End If Next i End With MsgBox "Done" Application.ScreenUpdating = True End Sub