Deleting Formulas Taking Excessive Time

[gwm]

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.)

 

[Norie]

How are you actually deleting the formulae?

Can you post some of your code?

 

[gwm]

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]

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]

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]

What are the names of the sheets you want to clear the formulae from?

 

[Smitty]

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]

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]

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?

 

[Legacy 1363]

What line of code in your macro is taking a long time?