Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Compare 3 cols and delete rows if match (Excel xp)

    I have a worksheet with 25,000 rows of names, addresses etc. Col D has first name, col F has last name and COL I has zip code. I need to delete all the rows that have duplicates. For instance, if there are 3 different rows that have John and Smith and 60300 then I have to delete the 2nd and 3rd rows which are duplicates. I looked at the pearson.com site but don't know how to manipulate that code to work for my situation. Thank you for the help.

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

    Re: Compare 3 cols and delete rows if match (Excel xp)

    Does this do what you want:

    <pre>Option Explicit
    Public Sub DeleteDups()
    Dim lLastRow As Long, I As Long
    lLastRow = ActiveSheet.Range("D65536").End(xlUp).Row - 1
    For I = lLastRow To 1 Step -1
    If Range("D1").Offset(I, 0).Value = Range("D1").Offset(I - 1, 0).Value And _
    Range("F1").Offset(I, 0).Value = Range("F1").Offset(I - 1, 0).Value And _
    Range("F1").Offset(I, 0).Value = Range("F1").Offset(I - 1, 0).Value Then
    Range("F1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compare 3 cols and delete rows if match (Excel xp)

    perfecto!!!!

Posting Permissions

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