I have a list of 6000 items that have data in multiple columns.
I want to be able to find duplicates in column A and delete the Row
I have tried everything from Advanced FIlters to Macros and both don't seem to work.
Anyone have a solution?
I have a list of 6000 items that have data in multiple columns.
I want to be able to find duplicates in column A and delete the Row
I have tried everything from Advanced FIlters to Macros and both don't seem to work.
Anyone have a solution?
any ideas?
Maybe these links will help
http://www.ozgrid.com/VBA/RemoveDuplicates.htm
http://www.vbaexpress.com/kb/getarticle.php?kb_id=135
http://www.cpearson.com/excel/deleting.htm
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
Select the range to check then run this macro
Please Login or Register to view this content.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
You could try a VBA loop:
JasonPlease Login or Register to view this content.
Jason,Originally Posted by jasoncw
Using countif to detect duplicate is very good idea and very efficient. However there are two issues with this,
As the loop progresses and starts deleting the desired rows, the source range should progressively shorten but since lastRow value is fixed at the begining of the loop, range extends to the lower rows which were outside the range initially. Not desirable. Of course in this case it does not matter because we are assuming that all the bottom rows outside the range are blank.
2. This macro will not delete both the rows which have same value. It will delete one and retain the other. To explain let us say that row 10 and 6 have duplicate values. The loop first deletes row 10 but when it reaches row 6 it finds no duplicate because row 10 was already deleted.
To avoid these problems it might be a good idea to use the loop to first flag the rows ( eg color the duplicate cells ) and then run a loop to delete the flagged rows.
A V Veerkar
Hi, A V. Thanks for looking at my code and critiquing it.Originally Posted by avveerkar
1. Yes, I see what you mean. I should have taken the shrinking range into account.
2. I was under the impression that he did want to leave one of the duplicates in place. Maybe not, though. Leaving one of the duplicates was intentional.
Thanks.
Jason
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks