+ Reply to Thread
Results 1 to 7 of 7

Finding Duplicates & Deleting the ROw

  1. #1
    Registered User
    Join Date
    08-18-2007
    Posts
    54

    Finding Duplicates & Deleting the ROw

    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?

  2. #2
    Registered User
    Join Date
    08-18-2007
    Posts
    54
    any ideas?

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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 !!!

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    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

  5. #5
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    You could try a VBA loop:
    Please Login or Register  to view this content.
    Jason

  6. #6
    Forum Contributor
    Join Date
    01-10-2006
    Location
    Ahmedabad, India
    MS-Off Ver
    Office 2000
    Posts
    346
    Quote Originally Posted by jasoncw
    You could try a VBA loop:
    Please Login or Register  to view this content.
    Jason
    Jason,

    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

  7. #7
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Quote Originally Posted by avveerkar
    Jason,

    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.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1