Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Duplicates (2002)

    I need to create a macro to delete a row if a duplicate exist in the column. Please advise. Thanks a bunch in advance for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Duplicates (2002)

    I assume you're looking for duplicates in column C. If found, which row should be deleted - the first one, or the last one, or a specific one?

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates (2002)

    I would like to delted the 2nd & 3rd (if a 3rd exists).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Duplicates (2002)

    Here you go:

    Sub DeleteDups()
    Dim r As Long
    Dim n As Long
    n = Range("C65536").End(xlUp).Row
    For r = n To 2 Step -1
    If Range("C" & r) = Range("C" & (r - 1)) Then
    Range("C" & r).EntireRow.Delete
    End If
    Next r
    End Sub

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Duplicates (2002)

    Hi Hans

    I was just thinking when I was looking at your code that this will work fine assuming that that column C is sorted in ascending or descending order so that it can compare the previous row. So in case it was not sorted in any shape or form I added this to your code


    Sub DeleteDups()
    Dim r As Long
    Dim n As Long

    <span style="background-color: #FFFFFF;"><font color=black>Columns("C:C").Select
    Range("A1:E14").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal</font color=black></span hiwhite>
    n = Range("C65536").End(xlUp).Row
    For r = n To 2 Step -1
    If Range("C" & r) = Range("C" & (r - 1)) Then
    Range("C" & r).EntireRow.Delete
    End If
    Next r
    End Sub

    Would that be a fair comment/addition? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Duplicates (2002)

    Yep, thanks! My implicit assumption that the data would already be sorted was unwarranted.

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

    Re: Delete Duplicates (2002)

    The code below does not require that the rows be sorted like Hans' code and does not sort them like Steve's.

    <code>
    Public Sub DelDups()
    Dim I As Long, J As Long
    For I = Range("C65536").End(xlUp).Row - 1 To 1 Step -1
    For J = I - 1 To 0 Step -1
    If Range("C1").Offset(I, 0).Value = Range("C1").Offset(J, 0).Value Then
    Range("C1").Offset(I, 0).EntireRow.Delete
    Exit For
    End If
    Next J
    Next I
    End Sub
    </code>
    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates (2002)

    You guys are the BEST! Thanks! Hans,Jezza & LegareColeman! Have a Fanastic weekend!

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates (2002)

    Legare,
    Would it be possible to edit this code so instead of deleting the row, the duplicate row(s) would be shaded or highlighted to allow the user to delete the row manually, if desired?

    Tom

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Duplicates (2002)

    Simply change the line

    Range("C1").Offset(I, 0).EntireRow.Delete

    to

    Range("C1").Offset(I, 0).EntireRow.Interior.ColorIndex = 6

    (If you haven't changed the color palette, ColorIndex 6 is yellow)

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

    Re: Delete Duplicates (2002)

    This will turn the background color of the duplicate rows Yellow.

    <code>
    Public Sub DelDups()
    Dim I As Long, J As Long
    For I = Range("C65536").End(xlUp).Row - 1 To 1 Step -1
    For J = I - 1 To 0 Step -1
    If Range("C1").Offset(I, 0).Value = Range("C1").Offset(J, 0).Value Then
    Range("C1").Offset(I, 0).EntireRow.Interior.ColorIndex = 6
    Exit For
    End If
    Next J
    Next I
    End Sub
    </code>
    Legare Coleman

  12. #12
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates (2002)

    Thank you both for the code, it worked great!

    Can I ask for one more favor?
    In the rows that contain duplicates, I would like to place a "x" in a column to the right of the data (Column F in the example), so I can select the "x" and look at the duplicates using an auto-filter.

    Once again, thanks for your help!

    Tom

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

    Re: Delete Duplicates (2002)

    Does this do what you want?

    <code>
    Public Sub DelDups()
    Dim I As Long, J As Long
    For I = Range("C65536").End(xlUp).Row - 1 To 1 Step -1
    For J = I - 1 To 0 Step -1
    If Range("C1").Offset(I, 0).Value = Range("C1").Offset(J, 0).Value Then
    Range("F1").Offset(I, 0).Value = "X"
    Exit For
    End If
    Next J
    Next I
    End Sub
    </code>
    Legare Coleman

  14. #14
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates (2002)

    ABSOLUTELY!

    MANY THANKS!!!!!

    Tom

  15. #15
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates (2002)

    Is there a way to delete the duplicate in "C3" but add the data in "D3" to "D2" and so on. example C2 has the first item. C3 is a duplicate. Concatenate "D2" & "D3" with the date separated by a comma? then delete row 3 (and so forth and so on)? I'm not even sure if this is possible.

Page 1 of 2 12 LastLast

Posting Permissions

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