Macro to delete duplicate/triplicate rows

[Ashleigh K.]

Hello All,

I am working with a very large database of first/last names and job titles.
Many of the entries in the database are repeated (up to four entries of the
same name). I am trying to write a macro so that if the last name, first
name and job title fields match the cells in the row below, then delete that
row. I have had some success with the macro written below, however I run
into problems when there are more than two entries of the same name. Does
anyone have any ideas as to how I might rewrite this macro so that it will
delete all of the duplicates (triplicates, etc)?

Sub Delete Row ( )
For i = 1 To 29
If Cells(i, 1) = Cells(i + 1, 1) And Cells(i, 2) = Cells(i + 1, 2) And
Cells(i, 3) = Cells(i + 1, 3) Then
Cells(i + 1, 1).Rows.EntireRow.Delete

Thanks in advance for any help,

A.

 

[Tom Ogilvy]

Sub Delete Row ( )
For i = 30 To 2 step -1
If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) _
And Cells(i, 3) = Cells(i - 1, 3) Then
Cells(i , 1).Rows.EntireRow.Delete
End if
Next
End Sub

Looping backwards should solve your problem.

 

[Ken Wright]

Is this a one-off. In which case insert a helper column in front of Col A say, and then assuming
your data is in what would now be Cols B/C/D/E, in cell A1 put = B1&C1&D1&E1. Copy it down to the
bottom, and then copy the column and paste special as values. You can now use Chip pearson's
macro to delete all rows with duplicate entries in Col A. Then when done, just delete Col A.

http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows