+ Reply to Thread
Results 1 to 3 of 3

Delete duplicate rows in two Excel sheets

  1. #1
    Registered User
    Join Date
    01-17-2017
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    16

    Delete duplicate rows in two Excel sheets

    I have Sheet1 and Sheet2 which I need to compare. In Sheet1 I need to delete those rows which are in Sheet2 and keep only those which are not or which are different (one or more values in 7columns). I have written following code so far, but it works so slowly, because I have more then 13000 rows in Sheet1. Is there any possibility to rewrite this code to make it faster?

    Please Login or Register  to view this content.
    Many thanks

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Delete duplicate rows in two Excel sheets

    This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

    Whenever you find yourself creating a loop step back a bit and consider if there isn't a more efficient way. Loops should only be used as a last resort since they can be very slow particularly when many rows are involved.

    The fastest and most efficient way I know of doing this is to use an autofilter and filter for whatever is the relevant criteria. Then select all the filtered rows using the .SpecialCells(xlCellTypeVisible) construct, delete them all, finally remove the autofilter. i.e. you delete a whole block of rows with one instruction rather than the 1300 instructions required by your loop.

    So create a helper column in both sheets 1 & 2 which concatenates all your 7 column values into a single cell. Then use a MATCH function to find each sheet1 helper column value in the sheet2 helper column. Then filter sheet1 helper column for the NOT #N/A values, select all the rows and delete them.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-17-2017
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    16

    Re: Delete duplicate rows in two Excel sheets

    Many thanks it works

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete duplicate rows between sheets
    By mohit999 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2016, 01:23 AM
  2. sum multiple sheets and delete duplicate rows
    By Farida in forum Excel General
    Replies: 1
    Last Post: 06-05-2012, 01:14 PM
  3. Delete duplicate rows and associated sheets
    By fatalcore in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-05-2012, 10:31 AM
  4. Compare 2 sheets and delete duplicate rows
    By Gavlar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-26-2011, 01:34 AM
  5. Delete both duplicate rows in excel
    By jayttae in forum Excel General
    Replies: 1
    Last Post: 07-22-2010, 09:13 PM
  6. Delete duplicate rows in Excel
    By vijay2482 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-28-2009, 10:32 AM
  7. Compare to Sheets and Delete Duplicate Rows...
    By BossWebmaster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2008, 11:19 AM

Tags for this Thread

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