Excel Made Easy

Create a Macro in VBA to search for the unique entries in a list and remove duplicates.

Having a long list of items with duplicate entries is very common in Excel. Sometimes you do not want these duplicates and need to make a new list without them.

In order to create a new list that contains only unique items you can of course do it manual with the filter function in Excel but if you want to automatize it, then you need to create a macro.

Let's take the list here under which is a list of fruits. We want to create a new list without the duplicates and put it next to it.

For this we are going to use the loop statement FOR...TO...NEXT and the conditional statement IF....THEN....ELSE.

vba remove duplicate

You want by pressing the button to start a VBA macro that create a new list on the right of the old one that contains only unique names

Let's think before we write code. This is the usual rule. Think before you write you code. Only genius people can write VBA code or any code without thinking. Usually what happens is you start typing code and it is fine for the beginning but once the first lines written you have to go back and make changes, and then add lines and then make try to remember what you wanted to do and then you code becomes so unreadable that you just start all over.

So here is the process we are going to use to make our VBA sort or remove duplicate algorithm.

1. I take the first item and put it in the final table
2. then I take the second item and browse the final table to see if it is there.
3. if not I put it in the final table in position ItemNb + 1
4. then I take the third item (i +1) of the first table '
5. then I browse the final table again and look search ' etc...

Here is the code associated with this algorithm.

Read the comment in it and it should be quite clear.

Sub remove_duplicatest()

Dim finalTable(50) As String

beginning = 6                ' first row of the table
itemNb = 0                   ' the number of unique items
alreadyHere = False      ' flag to say I saw it already
totalNumber = 50          'define some max items in the list
Column = 2                  ' the column of the list
finalColumn = 4            ' in which column will the unique sorted out item go.

For i = 0 + beginning To totalNumber + beginning    ' scan through the first table
    alreadyHere = False
    firstcolumnValue = Cells(i, Column).Value

    For j = 0 To itemNb    'we scan the final table

        If firstcolumnValue = finalTable(j) Then alreadyHere = True
   'if the firstcolumn value is already here we set alreadyhere to true

    Next j

    If alreadyHere = False Then       'if already here is still false then it is a new value

            finalTable(itemNb) = Cells(i, Column).Value   ' add the unique item to the table
            Cells(itemNb + beginning, finalColumn).Value = finalTable(itemNb)    ' display it in the excel sheet
        itemNb = itemNb + 1   'we increment to be ready for the next one.

    End If
Next i

End Sub

Here is the result.

vba remove duplicates

We hope you enjoyed this VBA article about removing duplicates in a list. It used different VBA statements like FOR...TO...NEXT, IF statement, Message box

This should give you enough to start VBA programming by yourself. Good Luck.

You can download the VBA remove duplicates in Excel here.

Please Tweet, Like or Share us if you enjoyed.