Deleting Formulas Taking Excessive Time

gwm

New Member
Joined
Jan 24, 2005
Messages
7
I'm running Excel 2002 on Windows 2000 V5.0.

I have a spreadsheet in which a user loads data into an input area. The spreadsheet has two buttons that execute VB macros. The first button is used to analyze the data and copies formulas onto several existing tabs. This first button typically takes about 3 minutes to execute. The second button simply deletes the formulas copied by the first button (deleting formulas in the reverse order that they were created) and restores the spreadsheet to the state it was in before the first button was pushed. This second button takes over 17 minutes to complete.

I'm trying to see if there is a way to get Excel to do the formula deletes quickly. (I'm assuming that Excel is trying to maintain cell recalculation flags or something and that is making the delete time long. If so, can this function be turned off? I turned the calculation mode to manual, but that doesn't seem to speed things up.)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
How are you actually deleting the formulae?

Can you post some of your code?
 

gwm

New Member
Joined
Jan 24, 2005
Messages
7
Here is a portion of the "Compress" macro that deletes the formulae. The ClearContents method is where things slow down.

Public Sub Compress()
' Turn Recalc Off
With Application
.Calculation = xlManual
.MaxChange = 0
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Application.Goto Reference:="DtlIGFOthPay!RC"
Range("A11..EF1510").ClearContents

Application.Goto Reference:="DtlIGFPV!RC"
Range("A11..EF1510").ClearContents

'Turn Recalc On
With Application
.Calculation = xlAutomatic
.MaxChange = 0
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate

Application.Goto Reference:="Input!RC"
Range("B11").Select

End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

This should be a bit faster:

<font face=Tahoma><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Compress()
    <SPAN style="color:#007F00">' Turn Recalc Off</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Application
        .Calculation = xlManual
        .MaxChange = 0
    
        ActiveWorkbook.PrecisionAsDisplayed = <SPAN style="color:#00007F">False</SPAN>
    
        Sheets("DtlIGFOthPay!RC").Range("A11:EF1510").ClearContents
        
        Sheets("DtlIGFPV!RC").Range("A11:EF1510").ClearContents
        
        <SPAN style="color:#007F00">'Turn Recalc On</SPAN>
        .Calculation = xlAutomatic
        .MaxChange = 0
        
        ActiveWorkbook.PrecisionAsDisplayed = <SPAN style="color:#00007F">False</SPAN>
        
        Calculate
        
        .Goto [<SPAN style="color:#007F00">'Input!RC'!B11]</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 

gwm

New Member
Joined
Jan 24, 2005
Messages
7

ADVERTISEMENT

Hmmm. The code looks a little cleaner, but now I get "Subscript out of range" error on line:

Sheets("DtlIGFOthPay!RC").Range("A11:EF1510").ClearContents

Is the syntax right?

(Thanks for the welcome to the board. :) )
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
What are the names of the sheets you want to clear the formulae from?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

The syntax is correct, but note that the sheet name needs to be exactly the same as the actual sheet. Even a leading or trailing space can throw off the code.

I copied your sheet names into a new WB and the code ran fine.

Smitty
 

gwm

New Member
Joined
Jan 24, 2005
Messages
7
I checked the sheet names and it looks ok, but I still got the error. So as a test I simply opened a blank worksheet and copied in the attached macro, and I still get the "Subscript out of range" error when it tries to clear the contents. Very strange.

Public Sub Compress()
' Turn Recalc Off
With Application
.Calculation = xlManual
.MaxChange = 0

ActiveWorkbook.PrecisionAsDisplayed = False

Sheets("Sheet1!RC").Range("J3:J4").ClearContents

'Turn Recalc On
.Calculation = xlAutomatic
.MaxChange = 0

ActiveWorkbook.PrecisionAsDisplayed = False

Calculate

End With
End Sub
 

gwm

New Member
Joined
Jan 24, 2005
Messages
7
OK, I got the revised macro working. It did require a syntax change. The

Sheets("DtlIGFOthPay!RC").Range("A11:EF1510").ClearContents

needed to be changed to:

Sheets("DtlIGFOthPay").Range("A11:EF1510").ClearContents

But even after the change, there was not a significant difference in the time it took to do the delete. Any other ideas for speeding up the deleting of a large number of formulas?
 

Forum statistics

Threads
1,148,056
Messages
5,744,548
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top