Speeding up macros in Excel

[a94andwi]

Hello.

I have a big problem with the way excel handles my macros. I have created several macros that starts advanced filtering of approximately 9000 rows and 35 columns.

It takes very long time each time I start one macro. I have disabled the automatic recounting and I have tried to reduce the code as much as possible in the vba editor.

Do you have any tip on how I can speed excel up even more?

/Anders

[davesexcel]

lets see the code

[a94andwi]

I am not able to let you see all code due to its size. It is approximately 70 macros. I give you one example here. Most of the other macros are similar to number one below.

1: The macro that I have created a button for which run two functions Filter clear inputs and Filter data.

Sub Filter_Varulager()
Application.Run "Filter_Clear_inputs"
Range("C25").Select
ActiveCell.FormulaR1C1 = "<>1928"
Range("AP25").Select
ActiveCell.FormulaR1C1 = "<>H"
Range("AN25").Select
Application.Run "FilterData"
End Sub

2. Filter Clear Inputs function

Sub Filter_Clear_inputs()
Range("A25:IV33").Select
Selection.ClearContents
Range("A26").Select
ActiveCell.FormulaR1C1 = "*"
Range("A27").Select
ActiveCell.FormulaR1C1 = "*"
Range("A28").Select
ActiveCell.FormulaR1C1 = "*"
Range("A29").Select
ActiveCell.FormulaR1C1 = "*"
Range("A30").Select
ActiveCell.FormulaR1C1 = "*"
Range("A31").Select
ActiveCell.FormulaR1C1 = "*"
Range("A32").Select
ActiveCell.FormulaR1C1 = "*"
Range("A33").Select
ActiveCell.FormulaR1C1 = "*"
Range("A25").Select
Application.Run "FilterData"
End Sub

3. Filter data function

Sub FilterData()
Range("Databas").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("FilterCriteria"), Unique:=False
Range("A24").Select

End Sub


Can you see any code that makes it run slowly?

Thank you.

/Anders

[davesexcel]

You could decrease the amount of times you use select, this would speed up the codes, something like this

Please Login or Register  to view this content.

you could use the code
Application.ScreenUpdating = False
before a macro to increase the speed as well

[a94andwi]

Hello.

Thank you for your help. Unfortunately it did not speed the whole file up efficient enough.

What can I do more?

[JR@SGC]

Hi

try the following:

before macro:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Your code

At the end of the macro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

NOTE: Application.Calculation turns of calculating so it may look like it has crashed as nothing on screen will change untill the end of the macro when it is turned back on


hope it helps

[a94andwi]

What is the difference between having manual calculations turned on in the options menu and the code you have written.

Does it really speed things up?

I am confused...:-)

/Anders