[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-03-26
Medium Priority
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

Accepted Solution

samb39 earned 500 total points
ID: 16301728
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

by:Jeroen Rosink
Jeroen Rosink earned 500 total points
ID: 16313504
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: http://members.iinet.net.au/~brettdj


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

830 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