VBA running very slow

[Akashwani]

Hi,

I'm using the following code and it takes over 20 seconds to complete what I thought was a simple process

Sub CopyDeleteData()
Dim t As Date
t = Now()

Application.ScreenUpdating = False
Application.Calculation = xlManual
ActiveSheet.Unprotect ("Password1")

With ActiveSheet
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    .Cells(lRow, 1).Value = Range("I4")
    .Cells(lRow, 2).Value = Range("J4")
    .Cells(lRow, 3).Value = Range("K4")
    .Cells(lRow, 4).Value = Range("L4")
    '.Cells(lRow, 5).Value = ActiveSheet.Range("F11")
End With
    
    Range("I4:L4").Select
    Selection.ClearContents
    Range("I4").Select

ActiveSheet.Protect ("Password1")

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

MsgBox Format(Now() - t, "hh:mm:ss")
End Sub

The workbook is 616kb it contains 2 tables, 6 charts, hundreds of Sumproduct formulas and a few dozen Index/Match.
Why is this so slow? I have run the code with Application.Calculation commented out and it takes about 1m 10s

Any suggestions on how to speed things up? This workbook is going to grow massively once it starts to be used daily and I cannot have the user waiting 22 seconds now let alone what it may take in 6 months time.

Thanks

Ak

 

[poolhall]

Akashwani,

instead of Rows.Count try to use a particular value representing a number of rows beyond which your data won't extend.

 

[Smitty]

Hi Smitty,

Thanks for your contribution, but you've confused the life out of me now, which isn't difficult. I wouldn't know where to start with your suggestion, but I value your input.

Let's say your workbook is a series of monthly values, where you're adding in new values each month and performing calculations on the newly entered data. If you've already performed calculations on the previous months' data, then you might not need to do it again, so you can copy/paste values there instead of maintaining formulas that have already done their job. So you can take a single column of formulas and copy it to an unused range. Then when you import your data for the next month, apply those formulas, let them calculate, then value them out. The master formulas will be intact for the next month, but you don't kill your workbook's performance with unnecessary formulas.

 

[Akashwani]

Hi Smitty,

Before I explore that avenue, which still seems complicated, would you or any other viewer care to download the file and see how long it takes on your system? I hope this link works, I've never done this before.

Ak'sTestFile.xls

Sheet In Out cells I4:M4 need completing then click the Save Data button.

Thanks, it will be interesting to see if it is my system before I spend £200 on a new laptop.

Ak

 

[poolhall]

15 sec

 

[Akashwani]

Wow,
Thanks for that poolhall, would you class that as slow then?

Ak

 

[poolhall]

if you step into your code and manually go through it line by line, you will see that the code itself is fine. The delay is when the command Application.Calculation = xlAutomatic performs, i.e., when the entire workbook recalculates.

 

[Akashwani]

Yep, I noticed that the data copies over in a blink of an eye, then it takes about 22 seconds minus a blink to recalculate the workbook.

I've just timed the code, excluding the recalculation and it takes 00:00:00

Thanks for your contribution today poolhall.

Ak

 

[poolhall]

Akashwani,

on the list Suppliers, I replaced the reference $998 in all formulas to $100, and it reduced the macro execution time down to 2 seconds, which is acceptable.

So maybe you need to think about a dynamic reference in those formulas so that all your SUMPRODUCTs will work on the actual number of rows.

 

[Akashwani]

poolhall you are a diamond ray:

As soon as I read your last reply I knew what I had overlooked, sheet In Out is a Table so ALL formulas that refer to that Table will expand as new data is added to it. I reduced the Sumproduct range to 25 added new data to In Out and it took 2 seconds, 2 seconds!!!! Also the Sumproduct ranges went to 26 YES!

I can now have eggs with my beans on toast and I don't have to smash up my trusty old laptop, great.

Thanks again poolhall for your help, I really appreciate that you took the time to look at the file and spotting that issue.

Thanks also to Smitty and GTO

Edit: I've just change the range on sheet WeeklyTotals and it has taken 1 second!!!!!

Ak

 

[poolhall]

you're welcome