Solved

Delete rows if they are duplicates

Posted on 2016-11-30
3
35 Views
Last Modified: 2016-11-30
Hi,

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
EndWith

EndSub

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
EndWith

EndSub

Open in new window

0
Comment
Question by:Legolas786
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 36

Accepted Solution

by:
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

0
 
LVL 48

Expert Comment

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

Author Closing Comment

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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

690 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