VBA Remove only certain duplicates

[floggingmolly]

I have a spreadsheet with data in columns A thru F. In column A are contract #'s and in F are percentages. If there are duplicates contract #'s in A, and in column F one of the duplicate contracts is at 100% I need to delete the one that is not 100%. If neither is 100% I need to keep both. Is this possible with VBA?

 

[kweaver]

I vaguely understand...but, could you provide a few examples?

 

[kweaver]

Thought I had a macro, but didn't.

 

[floggingmolly]

I vaguely understand...but, could you provide a few examples?

I can send you an example. I don't think it's possible because the ones that are 100% turn green. The 100% isn't actually shown on the sheet. It feeds from data on another sheet. So basically I have duplicates where neither row is green meaning neither is at 100%. Then I have duplicates where 1 might be green since it's 100% and one is not green because it's below 100%. I need to delete the duplicates where one is green and the other is not. Wouldn't be a big deal except there's over 2000 entries on the sheet.

 

[Peter_SSs]

Welcome to the MrExcel board!

I can send you an example.

Please keep all correspondence in the forum. Refer to #4 of the Forum Rules
My signature block below has a link with options for posting small sets of sample data in your post.

 

[kweaver]

Does this accomplish it?

Sub molly()
Dim LR As Long, i As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
 For i = LR To 1 Step -1
  If (Cells(i, 6) = 1) And (WorksheetFunction.CountIf(Range("A1:A" & LR), Range("A" & i)) > 1) Then
    Rows(i).EntireRow.Delete
  Else
  End If
 Next i
End Sub