-3

hope you're all doing great.

I have a task that has been killing myself and I'd love some help. I have an Excel table that goes from A1:AC13528, but there are a lot of duplicate values. I need to delete the duplicates based on column A, that DO NOT have the word "Keep" on column AC. For a row to be eligible for deleting, it has to meet both conditions.

I hope to be helped and thanks in advance!

slora00
  • 1
  • We don't help people who don't help themselves. Please post the code you have tried indicating what doesn't work. – freeflow Oct 10 '22 at 20:07
  • I'm not sure I understand your question. Can you add more detail and what you have tried? If you have the word "Keep" in column AC why not just filter/delete based on that? – Glenn G Oct 10 '22 at 20:19

1 Answers1

0

If I understand you correctly ....

enter image description here

Below is a modified sub after I macro recording what I do manually :

Sub test()
'set the table range - change as needed
Set rg = Range("A1", Range("A" & Rows.Count).End(xlUp))
Set rg = rg.Resize(rg.Rows.Count, 29)

'conditional formatting for duplicate value in column A
With rg.Columns(1)
    .FormatConditions.Delete
    .FormatConditions.AddUniqueValues
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Interior.Color = vbYellow
End With

'filter the table range, column A with color, column AC without "Keep"
'then delete the filtered result
With rg
    .AutoFilter Field:=1, Criteria1:=RGB(255, _
        255, 0), Operator:=xlFilterCellColor
    .AutoFilter Field:=29, Criteria1:="="
    .Resize(rg.Rows.Count - 1, 1).Offset(1, 0).EntireRow.Delete
    .AutoFilter
    .FormatConditions.Delete
End With

End Sub

enter image description here

If you want to try the sub, make a copy of your workbook - then copy-paste the sub on the copied workbook and run it. Please note that the sub delete all duplicates but leave the one which has "keep" in column AC. As you can see, in column A, "d" appear twice and there's no "d" at all after running the sub.

karma
  • 1,999
  • 1
  • 10
  • 14