Deleting Duplicate Rows

[Guest]

How do I Remove/Delete duplicate rows based on row value not column value? I
can't base it on Column value because some cells in the column might be the
same value but the over record may be different. I need to be able to delete
actual duplicate rows.

Joel

 

[Guest]

To match the entire row to another row would require each cell in that row to
be compared to the orther row. Most of the time these type comparisons can
be limited to two or three cells that contain primary data. That is data
that matters if it is duplicated. The remainder of the cells might contain
data that is not used for computation purposes and do not affect the out come
of any future computations.
In that case you could use a For... Next loop starting with the last record
and working to the top like:

lastRow = Worksheets(yourSht).Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Worksheets(yourSht).Cells(1, Columns.Count).End(xlToLeft).Column
myRange = Worksheets(yourSht).Range("A1:A" & lastRow)
For i = lastRow To 2 Step -1
If Cells(i, 1) = Cells(i, 1).Offset(-1, 0) And Cells(i, 2) = _
Cells(i, 2).Offset(-1, 0) And Cells(i, 3 = Cells(i, 3).Offset(-1, 0) Then
Cells(i, 1).EntireRow.Delete
End If
Next

This snippet compares three data elements in each row to the same three
elements in the row above and if they match, considers the entire row to be a
duplicate and deletes the the bottom row, then move one row up. You will
need to to sort your data base before beginning for this snippet to work
properly.

If you must compare all of the data elements, the you would have to continue
with the And statements inside the If ... End If for each data field in the
row.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Sub abtest4()
Dim rng As Range, i As Integer, j As Integer
Set rng = Range("DataRange").Rows
For i = rng.Rows.Count To 2 Step -1
For j = i - 1 To 1 Step -1
If RowsEqual(rng(i), rng(j)) Then
rng(i).Delete
i = i - 1
j = i
End If
Next j
Next i
End Sub

No sorting required.

Alan Beban