Delete Duplicates based on 2 criteria 1st Acutal duplicate match Date

[xcruc1at3r]

Need to modify the macro from deleting duplicates based on 2 criteria 1st the duplicate and 2nd criteria dd/mm/yy


for e.g

Columns


A serial B Names B C quantity D Quality E Category ....G DATE..etc etc....


Should search for duplicates in Column B & for Dates In G


If found

1 Grapes 12 B+ ....... 19/08/11<<< previous Status

4 Grapes 30 A+.........20/08/11 << Current Status


Should delete the Previous Status....


Macro is as below need to modify :


Sub DeleteDups()


Dim x As Long

Dim LastRow As Long


LastRow = Range("B65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("B1:B" & x), Range("B" & x).Text) > 1 Then

Range("B" & x).EntireRow.Delete

End If

Next x


End Sub


would really appreciate

 

[Luke M]

Since you're wanting to delete older entries, I think you just need to have the COUNTIF work from the bottom up, like:

[pre]

Sub DeleteDups()

Dim x As Long
Dim LastRow As Long

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

End Sub

[/pre]

 

[xcruc1at3r]

SMART MOVE LUKE....tHUMPS UPPP

 

[xcruc1at3r]

Luke: Can it be modified to only clear the Duplicate data from the rows and Sort the data by G till 850!!!(Date)

 

[xcruc1at3r]

because it wont hamper the location ref in ref sheets...

 

[Luke M]

Not sure I understand the first part of your question, as the macro should only be deleting the duplicate rows currently. Is this not the case?


For the latter, it would probably be easiest to generate the code if you record yourself sorting col G (select the correct area, sort on col G) and then moving that snippet of code over into the main macro.

 

[xcruc1at3r]

when u are right when your code deletes the duplicates so in the linked sheet it gives ref error to avoid that if the rows should only be cleared and sorted by G (DATE) so all the cleared rows which are blank will be moves down right!! so no empties in between the data! :D