Slow execution of macro. 2

[cameronfraser]

I wrote a macro to go through a list of items and eliminate all repeating items. When I execute the macro it runs very slowly. I can actually make it go faster by stepping through the code. Also, when the code is running, if I hold down the space bar or any character key it runs faster. What is happening?

Here is the code that is running so slowly:
Do
If ActiveCell.Offset(1, 0) = ActiveCell Then
ActiveCell.Formula = ""
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""


Screen updating true or false has no affect. I do know that it is the line ActiveCell.Formula = "" that makes the macro go slow, because if I comment it out this macro blazes. Note: I also tried the line ActiveCell.Formula.Clear and it still turtled.

 

[ratman]

Consider this:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
nextcheck:
Do While Not IsEmpty(ActiveCell.Value)
If ActiveCell.Value = ActiveCell.Offset(1, 0) Then
ActiveCell.Clear
ActiveCell.Offset(1, 0).Select
GoTo nextcheck
End If
ActiveCell.Offset(1, 0).Select
Loop
Columns("A:A&quot.Select
Selection.Sort Key1:=Range("A1&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Please email me at drat@mediaone.net and we'll talk

Ratman

 

[cameronfraser]

Ratman had the fix. The key is to set calculation to manual. For some reason auto calculation slowed this macro way down. Here is the fix. New lines shown in red:

Application.Calculation = xlCalculationManual
Do
If ActiveCell.Offset(1, 0) = ActiveCell Then
ActiveCell.Formula = ""
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Application.Calculation = xlCalculationAutomatic
Very important to turn the auto calculation back on!!

Here is the Tip:
Imporve macro speed by setting calculation to manual.

 

[jennuhw]

When you change the setting to manual, does that change any cell formulas changing automatically? What exactly does it change?

 

[ratman]

go here for a full explanation:

http://www.decisionmodels.com/calcsecretsg.htm

Ratman

 

[ide]

in your code now the main problem, i think the
ActiveCell.Formula = ""
line. If your sheet contains a lot of formulas use
activecell.clearcontents

i tried to make faster:
Sub sdf6()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
i = 0
Do
If ActiveCell.Offset(i + 1, 0) = ActiveCell.Offset(i, 0) Then
ActiveCell.Offset(i, 0).ClearContents
End If
i = i + 1
Loop Until ActiveCell.Offset(i, 0) = ""
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "dsf"
End Sub

or the more efficient

Sub sdf7()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
i = 0
Set rngCLast = ActiveSheet.Range("A1&quot
For Each c In ActiveSheet.Range("A2:A25000&quot
If c = "" Then GoTo endFr
If rngCLast = c Then
rngCLast.ClearContents
End If
Set rngCLast = c
Next c
endFr:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "dsf"
End Sub

 

[ide]

ratman crowned with a star