Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows
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. "Jase4now" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Duplicate Rows
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 Jase4now wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting duplicate rows | Excel Programming | |||
VBA for deleting duplicate rows | Excel Programming | |||
Deleting Duplicate Rows | Excel Discussion (Misc queries) | |||
Deleting Duplicate Rows | Excel Programming | |||
Deleting duplicate rows.....there's more | Excel Programming |