Deleting Duplicate Rows

[Guest]

I looked through previous posts on deleting duplicate rows, but could not
find a solution to my particular problem.

I have a worksheet with 4,000 rows.

Here are three rows as an example:

row1 - c1Tim Jones c2 c3123 Main Street
row2 - c1Tim Jones c2 c3123 Main Street
row3 - c1Tim Jones c2%Joe c3987 Front Street

I'd like a macro or something that compares the ENITRE row, not just one
column. The solutions I've found so far would delete both rows 2&3 above,
while I only want to delete row 2. Any sugestions?

Thank you

 

[Frank Kabel]

Hi
one workaround:
1. create a helper column with a formula such as
=A1 & "^" & B1 & "^" & C1

now use one of the duplicate deletion procedures on this helper column

 

[Ken]

Assuming that the rows are sorted and that you want to
delete if the first three columns match:

Dim RowCount As Double
Dim ILoop As Double
Sub DeleteRows()

Application.ScreenUpdating = False

RowCount = ActiveSheet.UsedRange.Rows.Count
For ILoop = RowCount To 2 Step -1
If Cells(ILoop, 1) & Cells(ILoop, 2) & Cells(ILoop, 3)
= Cells(ILoop - 1, 1) & Cells(ILoop - 1, 2) & Cells(ILoop -
1, 3) Then
Rows(ILoop & ":" & ILoop).Delete
End If
Next ILoop

Application.ScreenUpdating = True

End Sub

 

[Myrna Larson]

Have you tried the Advanced Filter, checking the Unique Records box? You can
either paste the records to a new location or filter in place and copy the
visible rows manually.