Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Slow Worksheet Calculation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Slow Worksheet Calculation
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "A. Young" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Slow Worksheet Calculation
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. "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "A. Young" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Slow Worksheet Calculation
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" wrote: 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. "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "A. Young" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Slow Worksheet Calculation
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. "A. Young" wrote: 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" wrote: 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. "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "A. Young" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Calculation | Excel Discussion (Misc queries) | |||
worksheet very slow in calculation | New Users to Excel | |||
Slow Calculation | Excel Worksheet Functions | |||
Does this slow down calculation? | Excel Discussion (Misc queries) | |||
Slow calculation | Excel Programming |