Need an excel macro or sub which can find duplicate cells (of text) remove them and keep unique records

Posted on 2006-03-26
Last Modified: 2011-09-20
I need a sub in VBA excel which will remove duplicate or even triplicate records - and keep only unique cells of text.
Question by:zaza5586
    LVL 4

    Accepted Solution

    Here's a menu command that does it:

     Filter for unique records  
    Select the column or click a cell in the range or list you want to filter.
    On the Data menu, point to Filter, and then click Advanced Filter.
    Do one of the following.
    To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place.
    To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference.
    To select a cell, click Collapse Dialog  to temporarily hide the dialog box. Select the cell on the worksheet, and then press Expand Dialog .

    Select the Unique records only check box.
    Note   Once you filter for unique values, you can copy them to another worksheet and then archive or delete the original worksheet.

    Here's the macro that does it:

    Sub Macro3()
    ' Macro3 Macro
    ' Macro recorded 3/27/2006 by Sam

        Range("A1:A12").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    End Sub

    LVL 33

    Assisted Solution

    You also might take a look at a free excel add-in which is developed by one of our Excel geniusses. It can be downloaded from:


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    The viewer will learn common shortcuts with easy ways to remember them. The viewer will then learn where to find all of the keyboard shortcuts, how to create/change them, and how to speed up their workflow.
    The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now