Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Delete Duplicate Rows

    Hello,

    Codes below are very slow on larger list of rows, deleting duplicate records and leaving only unique records. Is there any ways to make it faster, more faster on large database, or any other more faster codes.
    Code:
    Sub Delete_Duplicates()
    Dim i As Long
    Dim j As Long
    Dim ROW_DELETED As Boolean
    i = 1   
    Application.ScreenUpdating = False
    Do While i <= ActiveSheet.UsedRange.Rows.Count
        ROW_DELETED = False
        For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
            If Cells(i, 6) = Cells(j, 6) Then
                Rows(i).Delete
                ROW_DELETED = True
                Exit For
            End If
        Next j
        If Not ROW_DELETED Then i = i + 1
    Loop
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    For large datasets, a database is better suited to tasks like this than Excel - a SQL command could extract the unique addresses extremely quickly. If you have to use Excel, you could try getting PowerPivot from Microsoft which should enable you to extract a report of unique addresses quickly, provided you have Excel 2010 or 2013.
    Alternatively, if you can sort your addresses into an ordered list (not always practical, I know), you can extract the unique ones fairly quickly using code like this:

    Sub Delete_Duplicates()
    Dim i As Long
    Application.ScreenUpdating = False
    i = 1
    Do Until Cells(i, 6) = "" And Cells(i + 1, 6) = ""
    Do While Cells(i, 6) = Cells(i + 1, 6)
    Rows(i + 1).Delete
    Loop
    i = i + 1
    Loop
    Application.ScreenUpdating = True
    End Sub

  3. The Following User Says Thank You to unclehewie For This Useful Post:

    fjohan (2013-11-21)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Faster ways would be:
    1) If you have XL2007+, you could select column F and press the data - remove duplicate button
    2) use Advanced filter and to copy the unique records to a new worksheet and delete the original
    3) use an intermediate column with a countif formula to display the count of the items and then filter on this for the count >1 and delete those rows. You could then delete the intermediate column and autofilter

    No coding really required...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    fjohan (2013-11-21)

Posting Permissions

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