Removing Duplicate Rows (Excel 97) (Excel 97)

Topic

New Reply

To any one who can help. I have a file that has several hundred rows of data. Some of the rows contain exactly the same information across all columns. I need a way to scan the rows and automatically delete the duplicate rows.

Any help is greatly appreciated.

Thanks Brad

Reply | Quote

Replies

Advanced filter
Copy unique records to another location or do it in place

Steve

Reply | Quote

The VBA code below will delete all duplicate rows on the active worksheet:

Public Sub DelDupRows()
Dim I As Long, J As Long, K As Long
Dim lRMax As Long, lCMax As Long
    With ActiveSheet
        lRMax = .UsedRange.Row + .UsedRange.Rows.Count - 2
        lCMax = .UsedRange.Column + .UsedRange.Columns.Count - 2
        For I = lRMax To 1 Step -1
            For J = I - 1 To 0 Step -1
                For K = 0 To lCMax
                    If .Range("A1").Offset(I, K).Value  .Range("A1").Offset(J, K).Value Then Exit For
                Next K
                If K > lCMax Then
                    .Range("A1").Offset(I, K).EntireRow.Delete
                End If
                Exit For
            Next J
        Next I
    End With
End Sub

Reply | Quote

To all that replied. Thanks! The macro did the job.

Brad

Reply | Quote