Excel Remove Duplicate

[Excelnoobisme]

Im trying to remove duplicate line but excel always kept the 1st record and delete other. Is there anyway i can keep the last record and delete all other?

 

[mirabeau]

You can easily enough do it with VBA code.

Is that the sort of approach you're looking for?

If they're duplicates, does it matter which one you keep, other than sheet location, or perhaps formatting?

 

[Excelnoobisme]

Hi, im want to do it with vba. My approach is to keep the last record of duplicate from searching from Column B. Any1 can help?

 

[jeffreybrown]

How about this...

Assumes you have a header on column B

Sub DelDups()
    Dim LR As Long
    Dim r As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    For r = LR To 2 Step -1
        If Application.WorksheetFunction.CountIf(Range("B2:B" & r), Range("B" & r).Text) > 1 Then
            Range("B" & r).EntireRow.Delete
        End If
    Next r
End Sub

 

[Excelnoobisme]

Thanks Jeff for the reply. But it still remove the last record and keep the 1st record.

 

[jeffreybrown]

Yes I just realized I got it backwards. Give me a minute.

Will there be more than one duplicate and is the list sorted?

 

[Excelnoobisme]

Hi jeff,

There are more than 1 duplicate...the list is not sorted...

 

[jeffreybrown]

Yes, but by more than 1 does that mean 2 or could be many?

 

[Excelnoobisme]

i believe not more than 2 duplicate, usually 1.

 

[jeffreybrown]

Well just to be safe let's go with this

Sub DelDup()
Dim LR As Long

    For LR = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
        If Cells(LR, "B") = Cells(LR, "B").Offset(-1, 0) Then
           Cells(LR, "B").Offset(-1, 0).EntireRow.Delete
        End If
    
    Next LR
End Sub

Sort the list and then run. I need be we can build the sort into the code.