Can I speed this simple routine up?

[EssKayKay]

Hello,
I have a sub that is doing what I want. However, it takes quite a while to run (15+ seconds).
No biggie here - I was wondering if there is something I could do to speed this up a bit.

Here is my code:

Sub MinData()
Application.ScreenUpdating = False
Application.EnableEvents = False
   Range(Range("T14").Value & ":" & Range("T13").Value + 2).Delete Shift:=xlUp
End Sub

The delete range varies (T14 could be as high as row 2000 albeit doubtful).
In my test example -
T13 = 185
T14 = 386

Thanks for viewing,
Steve K.

 

[MARK858]

What exactly do you have in the cells being deleted?

By the way you need to turn

Application.EnableEvents = False

back on before exiting the sub


I would also switch T13 and T14 in your code, it doesn't (noticeably) affect the code but it is more logical to have the lowest number first

 

[EssKayKay]

What exactly do you have in the cells being deleted?

By the way you need to turn

Application.EnableEvents = False

back on before exiting the sub


I would also switch T13 and T14 in your code, it doesn't (noticeably) affect the code but it is more logical to have the lowest number first

Thank you Mark for getting back to me. This is an ongoing project I’m just playing with. It is an loan amortization sheet. There are numerous involved formulas throughout all of the rows I wish to delete. Without posting a sample of my worksheet following is what is in row 217 in columns B:H. The same formulas are in all other rows I wish to delete. I should note there is also data in other columns but this is the brunt of it. Column-B =IF(K216="","",IF(OR(B216>=nper,ROUND(K216,2)<=0),"",B216+1))

Column-C

=IF(B217="","",IF(OR(periods_per_year=26,periods_per_year=52),IF(periods_per_year=26,IF(B217=1,fpdate,C216+14),IF(periods_per_year=52,IF(B217=1,fpdate,C216+7),"n/a")),IF(periods_per_year=24,DATE(YEAR(fpdate),MONTH(fpdate)+(B217-1)/2+IF(AND(DAY(fpdate)>=15,MOD(B217,2)=0),1,0),IF(MOD(B217,2)=0,IF(DAY(fpdate)>=15,DAY(fpdate)-14,DAY(fpdate)+14),DAY(fpdate))),IF(DAY(DATE(YEAR(fpdate),MONTH(fpdate)+B217-1,DAY(fpdate)))<>DAY(fpdate),DATE(YEAR(fpdate),MONTH(fpdate)+B217,0),DATE(YEAR(fpdate),MONTH(fpdate)+B217-1,DAY(fpdate)))))) Column-D =IF(B217="","",IF(MOD(B217,periods_per_year)=0,B217/periods_per_year,"")) Column-E =IF(B217="","",start_rate) Column-F =IF(B217="","",ROUND((((1+E217/CP)^(CP/periods_per_year))-1)*K216,2)) Column-G =IF(B217="","",IF(B217=nper,K216+F217,MIN(K216+F217,IF(E217=E216,G216,IF($F$12="Acc Bi-Weekly",ROUND((-PMT(((1+E217/CP)^(CP/12))-1,(nper-B217+1)*12/26,K216))/2,2),IF($F$12="Acc Weekly",ROUND((-PMT(((1+E217/CP)^(CP/12))-1,(nper-B217+1)*12/52,K216))/4,2),ROUND(-PMT(((1+E217/CP)^(CP/periods_per_year))-1,nper-B217+1,K216),2))))))) Column-H =IF(OR(B217="",B217<$T$7),"",IF(K216<=G217,0,IF(IF(AND(B217>=$T$7,MOD(B217-$T$7,int)=0),$K$6,0)+G217>=K216+F217,K216+F217-G217,IF(AND(B217>=$T$7,MOD(B217-$T$7,int)=0),$K$6,0)+IF(IF(AND(B217>=$T$7,MOD(B217-$T$7,int)=0),$K$6,0)+IF(MOD(B217-$T$9,periods_per_year)=0,$K$7,0)+G217<K216+F217,IF(MOD(B217-$T$9,periods_per_year)=0,$K$7,0),K216+F217-IF(AND(B217>=$T$7,MOD(B217-$T$7,int)=0),$K$6,0)-G217))))

Also, I did switch the order of T13 & T14 around. It actually caused an error elsewhere in the worksheet so I put it back. I will look at that later. Thanks again, Steve

 

[MARK858]

How does switching the order in the code cause errors elsewhere?

As for speeding your code up, try setting your calculation to manual at the start of your code and setting it back to automatic at the end of your code.

 

[EssKayKay]

How does switching the order in the code cause errors elsewhere?

As for speeding your code up, try setting your calculation to manual at the start of your code and setting it back to automatic at the end of your code.

I found my problem so switching T13/T14 works fine. I made a copy of the line before editing and forgot to remove the old line.

By manual calculation I assume you mean placing this first in the routine
Worksheets("Amortize").EnableCalculation = False

If so, it did not speed things up - still around 17 seconds.

 

[MARK858]

I meant more

Application.Calculation = xlCalculationManual

i.e

Sub MinData()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayStatusBar = False
    End With
  
    Range(Range("T14").Value & ":" & Range("T13").Value + 2).Delete Shift:=xlUp
   
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayStatusBar = True
    End With

End Sub

 

[EssKayKay]

I meant more

Application.Calculation = xlCalculationManual

i.e

Sub MinData()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayStatusBar = False
    End With
 
    Range(Range("T14").Value & ":" & Range("T13").Value + 2).Delete Shift:=xlUp
  
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayStatusBar = True
    End With

End Sub

And again Mark - thank you for your time and consideration.
I tried your code but still taking nearly 20 seconds to run. Maybe there is something else going on here. I will look closer and see. If/when I figure anything out, I'll be back.

Again, much appreciated,
Steve

 

[RoryA]

Do you have other workbooks open? If not, is your current one always slow to recalculate when you change things?

 

[EssKayKay]

Do you have other workbooks open? If not, is your current one always slow to recalculate when you change things?

This is the only workbook open. The other recalcs (and there are many) are a bit slow (1-4 seconds) but nothing like this routine. The upside is this routine is run very seldom so if the user has to wait, so be it. I still am going to "try" see if something else may be causing this. However, I'm not a programmer so this may be an effort in futility - but I will try.

Thanks Rory,
Steve

 

[RoryA]

If you delete a row manually, how long does it take for the workbook to recalculate and be ready again?