Delete rows if they are duplicates

Posted on 2016-11-30
Last Modified: 2016-11-30

I have data in columns A to columns I, what I am trying to achieve is to run some VBA coding which will remove any duplicates rows so only the one unique line remains, so for example if there are 2, 3, or 5 identical rows of data only 1 line will remain.  I only want to delete the rows if all rows match so column A to column I.

So far I have got to the following

Sub DeleteRows()

With ActiveSheet
Set Rng = Range("A1", Range("B1").End(xlDown))
Rng.RemoveDuplicates Columns:=Array(1,2), Header:=xlYes


Open in new window

But when I implement this code to help me with further columns the result is not what I expected as it looks like its deleted over half my data and data where i know there are no duplicates?

Any idea where i have gone wrong? thanks

Sub DeleteRows()

With ActiveSheet
Set Rng = Range("A1", Range("I1").End(xlDown))
Rng.RemoveDuplicates Columns:=Array(1,9), Header:=xlYes


Open in new window

Question by:Legolas786
LVL 35

Accepted Solution

Kimputer earned 500 total points
ID: 41907137
If too many are deleted, you probably want this instead:

Sub DeleteRows()

With ActiveSheet
Set Rng = Range("A1", Range("I1").End(xlDown))
Rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlYes
End With

End Sub

Open in new window

LVL 46

Expert Comment

by:Martin Liss
ID: 41907181
If Kimputer's solution doesn't work for you then please attach your workbook.

Author Closing Comment

ID: 41907191
Thanks, cant believe it was as simple as that

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 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