Removing Duplicates in an Excel Column

Usually when doing some 'copy' 'and paste' stuff would end with so many duplicates. It would be very tedious in manually editing them. when collecting sites for link exchanges, we will get lots of duplicate domains especially from google and wikipedia which most of the links.

Normally, we use advanced filter but something is wrong with it, its not filtering the character 'a'. I got two of them and the result still has two of them. Here is how i did it with advanced filter.

1. Click On an empty cell column
2. On the menu choose: Data -> Filter -> Advanced Filter
3. In the dialog box, type in the range of cells to filter at the List Range text box. e.g. type in $A$2:$A$1319 for cell A2 to cell A1319
4. On the Copy to text box type in the cell location of the output (filtered list)
5. Click on the Unique records only check box
6. Press OK

With the tool duplicate remover, we could remove repeating domain names in an instant. But if we are just simply collecting words and similar list that we become another story. Usually we collect word or phrases and paste them in an excel worksheet. but the question is how to filter the list and remove the duplicates? well i run unto the same problems and luckily i found a macro on the internet, so it saved me the time in creating one.

1. Open Excel.
2. Alt + F11 to open the Visual Basic Editor (VBE).
3. Insert-Module.
4. Paste the code below.
5. Close the VBE (Alt + Q or the X in the top-right corner).

Excel Macro


Option Explicit 
 
Sub DeleteDups() 
     
   Dim x               As Long 
   Dim LastRow         As Long 
     
   LastRow = Range("A65536").End(xlUp).Row 
   For x = LastRow To 1 Step -1 
       If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then 
           Range("A" & x).EntireRow.Delete 
       End If 
   Next x 
     
End Sub