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

 

[GTO]

I doubt that the code is really the cause, though you did say you already sped it up by killing calcs. Maybe a little quicker.

Sub CopyDeleteData()
Dim t As Date
    t = Now()
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    Sheet1.Protect "Password1", , , , True
    
    With ActiveSheet
        lrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        Range(.Cells(lrow, 4), .Cells(lrow, 4)).Value = .Range("I4:L4").Value
        .Range("I4:L4").ClearContents
        
    End With
    
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    
    MsgBox Format(Now() - t, "hh:mm:ss")
End Sub

 

[ColinKJ]

Hi Akashwani,

I just ran it in a new WB, and it was less than a second. Do you have any event handlers populated "Change", "Selection-Change".

You could try adding:

Application.EnableEvents = False
 
 
 
Application.EnableEvents = True

 

[Akashwani]

Hi,

Thanks for the replies.

I have deleted 3 charts that were copies, I have deleted some Worksheet Change code.

GTO when I ran your code it took 24s

Colin when I ran my code with your suggestion added it took 25s

I have checked the Workbook for formulas and I have 24 array formulas as well as 7 Named ranges using Offset!!
Is it the offset that is causing the slow processing speed or my 6 year old laptop?

Ak

 

[ColinKJ]

O' you didn't say you were running it on a "Steam Powered Processor".

!!6 year old laptop".

Sorry I don't have any more suggestions.

 

[Akashwani]

Hey we'll have less of that if you don't mind, I've only just received a pay rise and Excel calculates that it will take me 50 weeks to save up for a 2011 nuclear powered laptop, it did take 32s to calculate but I think it's accurate enough

Thanks for your contribution chaps, I guess you have just reconfirmed the glaringly obvious to me, I need to get someone else to do this work

Thanks.

Ak

 

[GTO]

My laptop came in a box marked "New & Improved. Now, all electric!" and this still sounds slow. What's the named ranges/offset bit?

 

[Smitty]

If it's at all possible you might want to consider having only a master set of formulas and everything else be data only. When you need to update the formulas you can apply them via code, then value them out.

When I've had large wb's that had performance issues because of loads of formulas (specially SUMRODUCT's) I've done that with lots of success.

HTH,

 

[Akashwani]

Hi GTO,

The Named Ranges and Offset are for the charts I have.
An example Offset is....

=OFFSET(MonthlyTotals!$C$3,COUNT(MonthlyTotals!$C$3:$C$14)-MonthlyTotals!$B$1,,MonthlyTotals!$B$1)

This allows me to display the number of months in MonthlyTotals!$B$1 on my chart. So if the user enters 6 in B1, then the last 6 months worth of data is displayed, this is for a "Dashboard" sheet that I use.

The Named Range that uses this Offset is applied to the Chart Series Values.
I use ALL the Offset formulas to do the same thing for different Charts.

I have dug out another Workbook that has 12+ charts, too many formulas to count and numerous Offset formulas and Worksheet change code, it also takes about 20s to run a similar macro.

I guess I'll have to start eating beans on toast every day so that I can get that new laptop sooner.

Ak

 

[Akashwani]

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.

Thanks

Ak