Find First Instance of Cell Value and Delete Corresponding Entire Row

[David04Ruiz]

Hi all, I am trying to get a code that goes down column A (starting with row 2) and looks for the first instance that a cell value appears. Upon detecting that, it will delete that entire row. I am using the following code I found from another user:

   Set ws = Sheets("Sheet6")  ' sheet with data
   col = 1  ' data column to check
   rw = 2  ' data start row
   For ctr = 1 To 9999  ' prevent endless loop
      If ws.Cells(rw, col) = "" Then: Exit For  ' end of data
      If ws.Cells(rw, col).Value <> ws.Cells(rw - 1, col).Value Then ' if does not match previous row
          Rows(rw).EntireRow.Delete  ' delete row
      End If
      rw = rw + 1  ' next row
   Next

Before:

After:

As you can see, it has an issue when the "first instance" is the only instance. The result should have deleted the first "D" value and the first "F" value. The user that I got the code from only had duplicated values, not a combination of duplicates and unique like me. Would anyone happen to know how to fix this or have an alternative I could try? Thank you in advance!

 

[Versonol]

I'm assuming this is the result you want:

Before :

After:

I've changed the code a bit to get the result above.

Set ws = Sheets("Sheet6")  ' sheet with data
col = 1  ' data column to check
Lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For I = Lrow To 2 Step -1 'Starts from Row 2
    If ws.Cells(I, col) = "" Then: Exit For  ' end of data
    If ws.Cells(I, col).Value = ws.Cells(I + 1, col).Value Then  ' if does not match previous row
        Rows(I).EntireRow.Delete  ' delete row
    End If

Next

 

[Versonol]

My bad I've read the question wrongly

 

[Versonol]

Try this :

Set ws = Sheets("Sheet6")  ' sheet with data
col = 1  ' data column to check
Lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For I = Lrow To 2 Step -1 'Starts from Row 2
    If ws.Cells(I, col) = "" Then: Exit For  ' end of data
    If ws.Cells(I, col).Value <> ws.Cells(I - 1, col).Value Then  ' if does not match previous row
        Rows(I).EntireRow.Delete  ' delete row
    End If

Next

It'll give you this result :

 

[bebo021999]

Try:

Sub TEST()
Dim lr&, cell As Range, u As Range
With Sheets("Sheet6")  ' sheet with data
    lr = .Cells(Rows.Count, "A").End(xlUp).Row ' row of last data in column A
    For Each cell In .Range("A2:A" & lr)
        If WorksheetFunction.CountIf(.Range("A2", cell), cell) = 1 Then ' if cell was found 1st time
            If u Is Nothing Then
                Set u = cell
            Else
                Set u = Union(u, cell) ' combine cells those found 1st time
            End If
        End If
    Next
End With
u.EntireRow.Delete
End Sub

 

[David04Ruiz]

Try this :

Set ws = Sheets("Sheet6")  ' sheet with data
col = 1  ' data column to check
Lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For I = Lrow To 2 Step -1 'Starts from Row 2
    If ws.Cells(I, col) = "" Then: Exit For  ' end of data
    If ws.Cells(I, col).Value <> ws.Cells(I - 1, col).Value Then  ' if does not match previous row
        Rows(I).EntireRow.Delete  ' delete row
    End If

Next

It'll give you this result :
View attachment 80410

This code worked. Thank you so much!