Very Slow Worksheet Calculation

A

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
 
R

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("A2:p32").Select
this will take you to the desired sheet for the range select.

if you code it like this...
sheets("yoursheetname").range("A2:p32").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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top