Solved

Quick way to remove duplicates with VBA

Posted on 2014-01-21
5
566 Views
Last Modified: 2014-01-21
I have trolled through EE and various other sites to find a definitive way to remove duplicates with VBA code but they all seem to take forever to complete, reason being that my spreadsheet has over 200000 rows.

Can any expert provide me with VBA code that will quickly remove duplicates from column A in my spreadsheet.

Thanks
0
Comment
Question by:Jagwarman
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39796447
Hi,

pls try Data / data Tools / Remove Duplicates and select the column you want

or like this

ActiveSheet.Range("$A$1:$B$7").RemoveDuplicates Columns:=1, Header:=xlNo

Open in new window



Regards
0
 

Author Comment

by:Jagwarman
ID: 39796557
It's not until I get a reply sometines that I realize I should have been more specific.

I need to find the duplicates in column A but remove the entire Row.

Sorry.
0
 

Author Comment

by:Jagwarman
ID: 39796560
Rgonzo1971 did you see my post to you Re:

search for text in a file in a folder from an open file
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39796562
HI,

pls try
    ActiveSheet.Range(Range("A1"), Cells(Range("A" & Cells.Rows.Count).End(xlUp).Row, Cells(1, Cells.Columns.Count).End(xlToLeft).Column)).RemoveDuplicates Columns:=1, Header:=xlNo

Open in new window

Regards
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39796588
Hi,

For the other question, could you open a new question

Regards
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

696 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