How to remove all duplicates from a column in excel using VBA only leaving rows that have no duplicate?

Question

I'm trying to delete all duplicate values from a column of numbers in Excel. I want the remaining column to only contain unique values from the original table.

I have tried using the RemoveDuplicates method but it only removes a single instance of a duplicate instead of all of them.

I have also tried using the following code but it has the same problem as RemoveDuplicates. I'm not sure why though since the "Unique" tag is set to "True".

{MYWORKSHEET]AdvancedFilter Action:= _
    xlFilterCopy, CopyToRange:=[MYWORKSHEET].Range("B1"), Unique:=True

I have only found one solution that should work theoretically that uses a nested For loop to iterate over every individual row and check if it is equivalent to any other row in the table. The only problem is that this crashes Excel on my machine because it has to loop so much. Is there any way to do this other than this brute force method?

Here is what I'm looking for:

|-------| |----------------| |-------------------|
| INPUT | | DESIRED OUTPUT | | WHAT I DONT WANT  |
|-------| |----------------| |-------------------|
| 11111 | |      11111     | |       11111       |
|-------| |----------------| |-------------------|
| 22222 | |      55555     | |       22222       |
|-------| |----------------| |-------------------|
| 33333 |                    |       33333       |
|-------|                    |-------------------|
| 22222 |                    |       55555       |
|-------|                    |-------------------|
| 33333 |
|-------|
| 55555 |
|-------|

Every number, even if it has duplicates, is a unique number. please answer @JimmyShoe question, Do you need to preserve the columns data? — GMalc
– GMalc, Commented Jun 20, 2019 at 20:02
@JimmyShoe No there's just the one column of data and I'm ok with deleting every duplicate entirely from the worksheet — Sean
– Sean, Commented Jun 20, 2019 at 20:25

Ron Rosenfeld · Accepted Answer · 2019-06-20 18:44:34Z

3

Use the Advanced Filter with a formula criteria of, for example: =COUNTIF($A$6:$A$11,A6)=1

Result:

If you need to use a formula instead, you can do something like:

=IFERROR(INDEX(inputTbl,AGGREGATE(15,6,1/(COUNTIF(inputTbl[Input],inputTbl[Input])=1) * ROW(inputTbl)-ROW(inputTbl[#Headers]),ROWS($1:1))),"")

edited Jun 20, 2019 at 18:44

answered Jun 20, 2019 at 18:35

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

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

1 Comment

Sean Over a year ago

This one works the best for my case, thanks for the solution!

Warcupine · Accepted Answer · 2019-06-20 18:39:43Z

You can load the values into a dictionary and set the value of the key to false, check if the key exists and if so change the value to true. Iterate through the dictionary and only dump keys that are false.

dim mydict as object
dim iter as long
dim lastrow as long
dim cellval as string
dim dictkey as variant

set mydict = createobject("Scripting.Dictionary")

with activesheet

    lastrow = .Cells(.Rows.Count, "ColumnLetter").End(xlUp).row

    for iter = 1 to lastrow
        cellval = .cells(iter, "ColumnLetter").value
        if not mydict.exists(cellval) then
            mydict.add cellval, False
        else
            mydict(cellval) = True
        end if
    next
    iter = 1
    for each dictkey in mydict
        if mydict(dictkey) = False then
            .cells(iter, "ColumnLetter").value = dictkey
            iter = iter + 1
        end if
    next
end with

Ahotch · Accepted Answer · 2019-06-20 19:37:58Z

Well, here's a macro to actually do the deletions, or at least get you started:

Private Sub CommandButton1_Click()
    Dim celll, rng As Range
    Dim strRow, strRowList As String
    Dim strRows() As String

    Set rng = Range("a1:a" & Range("a1").End(xlDown).Row)

    For Each celll In rng
        If celll.Address = rng(1, 1).Address Then
            If celll.Value = celll.Offset(1, 0).Value Then strRow = celll.Row
        ElseIf celll.Value = celll.Offset(-1, 0).Value Or celll.Value = celll.Offset(1, 0).Value Then
            strRow = celll.Row
        End If

        If strRowList = "" Then
            strRowList = strRow
        ElseIf strRow <> "" Then
            strRowList = strRowList & "," & strRow
        End If

        strRow = ""
    Next

    MsgBox (strRowList)

    strRows() = Split(strRowList, ",")

    For i = UBound(strRows) To 0 Step -1
        Rows(strRows(i)).Delete
    Next
End Sub

Collectives™ on Stack Overflow

How to remove all duplicates from a column in excel using VBA only leaving rows that have no duplicate?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related