listi.net

Listi.net

             


How to delete duplicate rows?

Today I got a request to delete duplicate rows from a spreadsheet. The following code will do this.

Note that the first loop starts from the last record and works backwards. You do this because as you delete records the loop variable stays correct. If you went forward you wouldn't know where the end was. It assumes that there are 2,139 rows in the sheet and 16 columns. The sheet could be modified to figure out the last row and last column.

The code uses two for loops and checks field by field to see if the data is a duplicate.


Sub RemoveDups()

Dim i As Long, j As Long, bDup As Boolean

For i = 2139 To 1 Step -1 ' last row hardcoded
    If i = 1 Then Exit For 'to preve error reference row 0
   
    bDup = True
    Cells(i, 1).Select  
   
    For j = 1 To 16 ' each column last hard coded
       
        If Cells(i, j) = Cells(i - 1, j) Then
        Else
            bDup = False
            Exit For
        End If
       
           
    Next
    If bDup Then
        Rows(i).EntireRow.Delete
    End If
   


Next