Remove row if duplicate ID# in column

**Usta** · 12-07-2012, 03:12 PM

I found the following elsewhere which works almost flawlessly, 'cept it removes the rows that I have already modified. How do I modify this so it removes the second row instead of the first?

Sub DelRowsColASame() s
    Dim Lastcell As Range 
    Dim I As Long 
     
    Set Lastcell = Cells.Find("*", Searchdirection:=xlPrevious) 
     
    If Not Lastcell Is Nothing Then 
         'sort the list
        Range(Cells(1, 1), Cells(Lastcell.Row, Lastcell.Column)).Sort _ 
        Cells(1, 1), xlAscending, Header:=xlYes 
         'delete out a previous row if 1st column matches
        For I = Lastcell.Row To 2 Step -1 
            If Cells(I, 1).Value = Cells(I - 1, 1).Value Then 
                Cells(I - 1, 1).EntireRow.Delete 
            End If 
        Next I 
    End If 
End Sub

**stanleydgromjr** · 12-07-2012, 03:23 PM

Usta,

Welcome to the Excel Forum.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Option Explicit
Sub DelRowsColASame()
Dim Lastcell As Range
Dim I As Long
Set Lastcell = Cells.Find("*", Searchdirection:=xlPrevious)
If Not Lastcell Is Nothing Then
  'sort the list
  Range(Cells(1, 1), Cells(Lastcell.Row, Lastcell.Column)).Sort Cells(1, 1), xlAscending, Header:=xlYes
  'delete out a previous row if 1st column matches
  For I = Lastcell.Row To 2 Step -1
    If Cells(I, 1).Value = Cells(I - 1, 1).Value Then
      Cells(I, 1).EntireRow.Delete
    End If
  Next I
End If
End Sub