Excessive time to run macro

[okeefe1961]

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

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

 

[Norie]

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]

Advance Filter is on the Data Ribon under the Sort & Filter Section.

Hope that helps.

 

[MrKowz]

If you really don't want to use filters, try turning off automatic calculations.