Finding Duplicates & Deleting the ROw

I have a list of 6000 items that have data in multiple columns.

I want to be able to find duplicates in column A and delete the Row

I have tried everything from Advanced FIlters to Macros and both don't seem to work.

Anyone have a solution?

any ideas?

You could try a VBA loop:

Dim i As Long, lastRow As Long

lastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row

For i = lastRow To 1 Step -1
    If Application.Countif(Range("A1:A" & lastRow), Range("A" & i)) > 1 Then _
        Rows(i).EntireRow.Delete
Next i

Jason

Originally Posted by jasoncw

You could try a VBA loop:

Dim i As Long, lastRow As Long

lastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row

For i = lastRow To 1 Step -1
    If Application.Countif(Range("A1:A" & lastRow), Range("A" & i)) > 1 Then _
        Rows(i).EntireRow.Delete
Next i

Jason

Jason,

Using countif to detect duplicate is very good idea and very efficient. However there are two issues with this,
As the loop progresses and starts deleting the desired rows, the source range should progressively shorten but since lastRow value is fixed at the begining of the loop, range extends to the lower rows which were outside the range initially. Not desirable. Of course in this case it does not matter because we are assuming that all the bottom rows outside the range are blank.
2. This macro will not delete both the rows which have same value. It will delete one and retain the other. To explain let us say that row 10 and 6 have duplicate values. The loop first deletes row 10 but when it reaches row 6 it finds no duplicate because row 10 was already deleted.
To avoid these problems it might be a good idea to use the loop to first flag the rows ( eg color the duplicate cells ) and then run a loop to delete the flagged rows.

A V Veerkar

Originally Posted by avveerkar

Jason,

Using countif to detect duplicate is very good idea and very efficient. However there are two issues with this,
As the loop progresses and starts deleting the desired rows, the source range should progressively shorten but since lastRow value is fixed at the begining of the loop, range extends to the lower rows which were outside the range initially. Not desirable. Of course in this case it does not matter because we are assuming that all the bottom rows outside the range are blank.
2. This macro will not delete both the rows which have same value. It will delete one and retain the other. To explain let us say that row 10 and 6 have duplicate values. The loop first deletes row 10 but when it reaches row 6 it finds no duplicate because row 10 was already deleted.
To avoid these problems it might be a good idea to use the loop to first flag the rows ( eg color the duplicate cells ) and then run a loop to delete the flagged rows.

A V Veerkar

Hi, A V. Thanks for looking at my code and critiquing it.

1. Yes, I see what you mean. I should have taken the shrinking range into account.

2. I was under the impression that he did want to leave one of the duplicates in place. Maybe not, though. Leaving one of the duplicates was intentional.

Thanks.

Jason