vba calculations too slow

[muizac]

Hello,
You may have able to solve my hair loss problems!!!

This spreadsheet is only 2mb but now takes 3 - 10 minutes to add a row or save the document - especially the 'transactions worksheet' - which was ok when it was 200 rows, but now I've got 1000 its a different story.

The file is over 100k ozgrid size so I uploaded it here:
https://rcpt.yousendit.com/1100581765/cfffdd249700b9b86b162ecba53fb630

I'm pretty good with excel but a newbie to vba (this was cobbled together from things on the net). Does the VBA need optimizing, or does something else need to change to speed it up?

Thanks heaps
Andrew

 

[diddi]

have you applied formatting to the whole sheet, or filled down a heap of formulae somewhere?

do you turn off autocalculation when you are executing vba?

 

[muizac]

Hi there,

I have a fair few sumproduct calculations going down about 5000 records.

I'm not sure how to "turn off autocalculation when executing vba" - but I have selected Manual in Formulas > Calculation options - is this the same thing?

It was working great - it's just since Ive put in these few hundred records it's stuffed up.

Thank you
A

 

[Michael M]

try adding this to your code

Sub test()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'your code here'


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Hi P: have a happy and restful easter

 

[muizac]

Beauty - thanks mate, I'll give that a whirl tonight.
Not sure how restful easter will be with ANZAC and 2UP - but I'll try
Have a good long weekend mate

 

[diddi]

greets M
hows the world up there

 

[Michael M]

Aargh !!!
5 days off ......I'm going insane !!
Just started "fixing" a Little "A"'s project.
I'm not the smartest, but some people simply shouldn't be allowed near a spreadsheet !!