Excessive time to run macro

okeefe1961

New Member
Joined
Aug 21, 2008
Messages
3
I have a file 64,00 rows by 109 columns ~ 4M in size, that contains multiple duplicate entries (~19,000 unique entries). I have the following macro to remove the duplicates but it takes in excess of 2 hours to run. Any idea's on why it is so slow / and or ways to improve its speed. I don't think its my machine

Core2 Duo @2.4GHz - 4G Ram
Windows XP Pro SP3
Excel 2007


Code:
Sub DelDupsRows()
Dim iListCount As Long, iCtr As Long
Dim cstr1 As String, Last_Audit_Sheet As String
Dim Source As Worksheet, start As Date, Finish As Date

start = Time

Set Source = Worksheets("Tmp_Audit_Trail")

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Range("A1048576").End(xlUp).Row

For iCtr = iListCount To 3 Step -1
   If Source.Cells(iCtr, 1).Value = Source.Cells(iCtr - 1, 1).Value Then
     Rows(iCtr & ":" & iCtr).Delete Shift:=xlUp
   End If
Next iCtr

Application.ScreenUpdating = True
Finish = Time
MsgBox "Done! Start- " & start & "  Finish- " & Finish

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
Why not try using Data>Advanced filter...? That has an option to only return unique values.

Though I don't know where that option will be in 2007, which you appear to be using.:)
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
Advance Filter is on the Data Ribon under the Sort & Filter Section.

Hope that helps.
 
Last edited by a moderator:

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
If you really don't want to use filters, try turning off automatic calculations.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,257
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top