Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Duplicate Rows (Excel 97) (Excel 97)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Removing Duplicate Rows (Excel 97) (Excel 97)

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

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Duplicate Rows (Excel 97) (Excel 97)

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

    <pre>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
    </pre>

    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Duplicate Rows (Excel 97) (Excel 97)

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

    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •