Delete rows in Excel based on duplicates in Column

Question

I want to delete the rows in Excel based on duplicate values in a column. Let's say

Column A      Column B
ABC             1
DEF             2
DEF             3
ABC             1

In column B, we have a duplicate value. Going with just the remove duplicate function doesn't help.

lancer102rus · Accepted Answer · 2016-07-12 11:01:46Z

0

Well, this code works perfectly

ActiveSheet.Range("$A$1:$B$4").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

answered Jul 12, 2016 at 11:01

lancer102rus

112 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Varun Fatehpuria Over a year ago

but this selects all the column. i have a lot of columns,just want to delete duplicates based on 1 column

lancer102rus Over a year ago

Just set instead of [column_number] your column and instead of [data_range] your table range ActiveSheet.Range([data_range]).RemoveDuplicates Columns:=[column_number], Header:=xlNo

Varun Fatehpuria Over a year ago

Nope doesnt seem to be working. Tried all variations of it

lancer102rus Over a year ago

show me please your variant. And it will be better if I could see your file

Varun Fatehpuria Over a year ago

I have a list of email ids in one column. So if there are duplicates, I would like the duplicated row to be deleted.

|

Maxime Porté · Accepted Answer · 2016-07-12 12:16:29Z

Maybe not the most optimized solution, but it does the work

Public Sub DeleteDuplicates()
    Dim rng As Range, cell As Range, find As Range, previous As Range
    Set rng = Worksheets("Test").Range("A:A")

    ' For each cell of the column
    For Each cell In rng
        ' Check duplicates only if the cell is not empty
        If Trim(cell) <> "" Then
            ' Search for the same value in column A
            Set find = rng.find(What:=cell, After:=cell, LookAt:=xlWhole, MatchCase:=True)
            ' if a result is found
            If Not find Is Nothing Then
                Do
                    Set previous = Nothing
                    ' If the column B is the same, save the row as "to be deleted"
                    ' The delete is not now otherwise the FindNext won't work
                    If find.Offset(, 1) = cell.Offset(, 1) Then
                        Set previous = find
                    End If

                    Set find = rng.FindNext(find)

                    ' If a duplicate was found, delete the raw
                    If Not previous Is Nothing Then
                        previous.EntireRow.Delete
                    End If

                    ' if no more duplicate, exit the do loop
                    If find Is Nothing Then
                        Exit Do
                    End If

                    ' if address of the result is the same as the clel, exit the do loop
                    If cell.Address = find.Address Then
                        Exit Do
                    End If
                Loop While True
            End If
        End If
    Next
End Sub

Collectives™ on Stack Overflow

Delete rows in Excel based on duplicates in Column

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related