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
            bDup = False
            Exit For
        End If
    If bDup Then
    End If

blog comments powered by Disqus