Clever Geek Handbook

Remove duplicates if they are together in excell column

I am trying to delete an entire row if duplicates are found together. If I don't find it together, I want to keep it without deleting it.

For an example Column A:
Apple,
Apple,
Orange,
Orange,
Apple,
Apple,

I need to have an output like:

Apple,
Orange,
Apple,

I am using the following code but have not yet achieved the desired result (only get Apple Orange). Any help is appreciated, thanks.

   Sub FindDuplicates()
   Dim LastRow, matchFoundIndex, iCntr As Long
   LastRow = Cells(Rows.Count, "A").End(xlUp).Row
   For iCntr = 1 To LastRow      
   If Cells(iCntr, 1) <> "" Then
   matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" &      LastRow), 0)
   If iCntr <> matchFoundIndex Then
   Cells(iCntr, 10) = "Duplicate"
   End If
   End If
   Next

   last = Cells(Rows.Count, "J").End(xlUp).Row ' check results col for values
   For i = last To 2 Step -1
   If (Cells(i, "J").Value) = "" Then
   Else
   Cells(i, "J").EntireRow.Delete  ' if values then delete
   End If
   Next i
   End Sub
+3


source to share


2 answers


Not as easy as

Dim LastRow As Long
Application.screenUpdating=False
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
        Cells(i, 1).EntireRow.Delete
    End If
Next i
Application.screenUpdating=True


solve it?

+5


source


Work from bottom to top and only delete if the cell is above the same value.

dim r as long

with worksheets("sheet1")
    for r = .cells(.rows.count, "A").end(xlup).row to 2 step -1
        if lcase(.cells(r, "A").value2) = lcase(.cells(r - 1, "A").value2) then
            .rows(r).entirerow.delete
        end if
    next r
end with


If you don't want the comparison to be case insensitive, remove the functions lcase

.

+2


source






More articles:


All Articles