Very Slow Worksheet Calculation

[A. Young]

I have code that runs on activation of a sheet to unprotect it, filter out
zero rows, and reprotect. Since I added this code to the workbook it
calculates very slowly - not only the sheet it runs on, but all sheets. I am
fairly new to use of VBA. See code:

Option Explicit
Private Sub Worksheet_Activate()

'
' Autofilter Macro
'

'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.Range("$A$1:$N$74").Autofilter Field:=1, Criteria1:="<>0", _
Operator:=xlAnd
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[ryguy7272]

You can perform calculations on a specific sheet. Found this in the archives;
post from FSt1:

add this to just before the range select
sheets("yoursheetname").activate
Range("A232").Select
this will take you to the desired sheet for the range select.

if you code it like this...
sheets("yoursheetname").range("A232").select
you will get an error - script out of range - if your are not on the
activesheet.
you can only select from the active sheet


HTH,
Ryan---

 

[A. Young]

Thank you for the suggestion.

I did try placing the code just before range select. It did not appear to
speed up the procedure at all. And calculation is still slow on other sheets.
The sheet that runs the macro is a summary page. It summarizes all financial
data from input pages throughout the workbook. I could filter the summary
page manually much faster than the code is doing it, but the person that is
going to be using the workbook has almost no knowledge of Excel. Automation
is essential.

 

[A. Young]

After working on the workbook to try to fix the problem I found that it was
not the vba code slowing the calculation, but the use of many sumproduct
functions on the summary page. Thank you for your help.

 

[A. Young]

So the problem is now that the summary page is summarizing financial data
from input sheets with 5 major categories each with its own subcategory list
- requiring sumproduct for 2003. But the whole workbook calculates so slow it
is not useful. Do I have to go to a user-defined fxn now or is there a way to
speed calculation in the workbook while keeping sumproduct on a single page?
Thanks again.