Delete Duplicate Rows, if column does not contain x

Hello!

I have an excel VB script/Macro that deletes duplicate rows, but I now need to expand it and I can't figure out how (& I’m limited for time):

What I want to do is: Compare columns A, B for duplicate rows, and if duplicate row exists, ONLY delete if column D does not say “Closed” or “Abandoned”.

Any suggestions on how to search sheet for duplicates rows based on column A B, and only delete row if column D of the duplicate row does not say “Closed” or “Abandoned”?

(At any given time, there is 100 to 3000 rows of data; thus, do not want to use filters or conditional formatting, or anything manual to visually search for duplicates and delete).

Example data:
123 John Apple Open ** This row would be deleted
124 Mary Apple Closed
125 Sara Apple Open
123 John Apple Closed
123 John Apple Pending ** This row would be deleted


Example delete duplicate VB/Macro I had previously used:
'delete duplicates rows
destSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Thank-you,
Julie!

Hi Julie
Here's another routine that I think does what you want. I've assumed it's sheet 1 & the top row of the data is row 5, change the numbers to suit.
It sorts the data into ascending order by column A & then column B,it doesn't delete any columns, only duplicate rows that don't contain Closed or Abandoned in column D.
As Trebor says, test it on a copy
Cheers

Please Login or Register  to view this content.

Hi Guys - I just got back to this today & I'll try these out and let you know. Thx J!