Delete duplicate rows macro needed - a little complicated

[WebAnalytics]

Please see below: need a macro that will delete the first two entire rows of duplicate order id's and keep the third

Any help?


[TABLE="width: 1063"]
<tbody>[TR]
[TD]Order Id[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]$24,893.66[/TD]
[TD="align: right"]305[/TD]
[TD="align: right"]$81.62[/TD]
[/TR]
[TR]
[TD="align: right"]36051480
[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]$817.38[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$817.38[/TD]
[/TR]
[TR]
[TD="align: right"]36051480[/TD]
[TD="align: right"]$0.00[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]$817.38[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$817.38[/TD]
[/TR]
[TR]
[TD="align: right"]36051480[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$817.38[/TD]
[TD]NULL[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$817.38[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$817.38[/TD]
[/TR]
[TR]
[TD="align: right"]36053154
[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]$749.50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$749.50[/TD]
[/TR]
[TR]
[TD="align: right"]36053154[/TD]
[TD="align: right"]$0.00[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]$749.50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$749.50[/TD]
[/TR]
[TR]
[TD="align: right"]36053154[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$749.50[/TD]
[TD]NULL[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$749.50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$749.50[/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

Just a few questions:

1. Are there always 3 entries for each ORDER ID, I mean a set of duplicates and 1 unique entry
2. Do the cells with dashes "-" only have values on the unique entry's line

 

[texasalynn]

try this

Sub DeleteDups()
Dim i, LR, RCnt As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    RCnt = WorksheetFunction.CountIf(Columns("A"), Cells(i, "A"))
    RCnt2 = WorksheetFunction.CountIf(Columns("F"), Cells(i, "F"))
        If (RCnt > 1 And Cells(i, "C") = "-") And _
            (RCnt2 > 1 And Cells(i, "E") = "-") Then
            Rows(i).Delete
        End If
Next i
MsgBox "Done"
End Sub

 

[WebAnalytics]

momentman,

to answer your question

1. Yes

2. not sure what you mean