| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet().Range.Calculate Problem
I am having a major problem with these two lines of code in my otherwise
great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet().Range.Calculate Problem
There's no need to select the worksheet in order to do the calculation. Does
the range you reference have a ton of functions in it that refer to macros? Do you have other macros firing every time a cell in calculated? For example, if you have code in the worksheet Change or Calculate Events it could take a while. This may help a little. Application.ScreenUpdating = False Worksheets("Goals Tracker").Range("G3:GA712").Calculate Application.ScreenUpdating = True Can you post the entire procedure and any other macros that are in the workbook? It would be more helpful. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Ayo" wrote: I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet().Range.Calculate Problem
There are lots of macros in the work book. I will have to send you the files.
But the only that is causing the problem is inside a Sub for a button on a Userform: Private Sub cmdUpdate_Click() Dim ws As Worksheet, c As Range Dim BO_Datafile_Name As String, BOReport_lastColumn As String Dim BOReport_lastRow As Integer, BOPos As Integer Dim BOReportWS As Worksheet, goalsTrackerWS As Worksheet Dim rngBOReport As Range Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Workbooks.Open filename:=Me.txtFilePath.Value, UpdateLinks:=0, ReadOnly:=True BO_Datafile_Name = ActiveWorkbook.Name Workbooks(BO_Datafile_Name).Worksheets("Sheet1").S elect Workbooks(BO_Datafile_Name).Worksheets("Sheet1").C opy Befo=Workbooks("AAV Daily Reports 2010_ver1.xls").ActiveSheet '"" CLOSE THE BO DOWNLOAD DATA FILE ""' Workbooks(BO_Datafile_Name).Activate Workbooks(BO_Datafile_Name).Close Workbooks("AAV Daily Reports 2010_ver1.xls").Worksheets("Sheet1").Activate BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count Set BOReportWS = Worksheets("BO Download") ThisWorkbook.Worksheets("Sheet1").Range("A4:AW" & BOReport_lastRow).Copy ThisWorkbook.Worksheets("BO Download").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("B4").Select ThisWorkbook.Worksheets("Sheet1").Delete BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row BOPos = InStr(1, BOReportWS.Range("IV3").End(xlToLeft).Address(Colu mnAbsolute:=False), "$", vbTextCompare) BOReport_lastColumn = Left(BOReportWS.Range("IV3").End(xlToLeft).Address (ColumnAbsolute:=False), BOPos - 1) Unload Me Worksheets("BO Download").Select Range("A2") = "This Report was generated on " & Format(Now, "mmmm d, yyyy h:mm:ss AMPM") & " (Eastern Time)" Range("B4").Select Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate '"" DELETE BO DOWNLOAD TAB, SAVE THE FILE AND CALCULATE THE WORKSHEET ""' Dim obj As OLEObject Dim filename As String, todaysDate As Date filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) 'Worksheets("Goals Tracker").Calculate Worksheets("Goals Tracker").Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Worksheets("BO Download").Select Worksheets("BO Download").Delete ActiveWorkbook.SaveAs filename & "_" & Format(Date, "mmmdd_yy") & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic End Sub "Ryan H" wrote: There's no need to select the worksheet in order to do the calculation. Does the range you reference have a ton of functions in it that refer to macros? Do you have other macros firing every time a cell in calculated? For example, if you have code in the worksheet Change or Calculate Events it could take a while. This may help a little. Application.ScreenUpdating = False Worksheets("Goals Tracker").Range("G3:GA712").Calculate Application.ScreenUpdating = True Can you post the entire procedure and any other macros that are in the workbook? It would be more helpful. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Ayo" wrote: I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet().Range.Calculate Problem
I don't see anything crazy in the code you posted that would be causing this
issue. Is there any macros in the "Goals Tracker" worksheet module? Is so, can you post those codes? Do the formulas or values in range Worksheets("Goals Tracker").Range("G3:GA712") linked to other workbooks? If so, do those workbooks have worksheet macros? What about the Workbook Level Modules? Any macros in the SheetCalculate or SheetChange Events? -- Cheers, Ryan "Ayo" wrote: There are lots of macros in the work book. I will have to send you the files. But the only that is causing the problem is inside a Sub for a button on a Userform: Private Sub cmdUpdate_Click() Dim ws As Worksheet, c As Range Dim BO_Datafile_Name As String, BOReport_lastColumn As String Dim BOReport_lastRow As Integer, BOPos As Integer Dim BOReportWS As Worksheet, goalsTrackerWS As Worksheet Dim rngBOReport As Range Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Workbooks.Open filename:=Me.txtFilePath.Value, UpdateLinks:=0, ReadOnly:=True BO_Datafile_Name = ActiveWorkbook.Name Workbooks(BO_Datafile_Name).Worksheets("Sheet1").S elect Workbooks(BO_Datafile_Name).Worksheets("Sheet1").C opy Befo=Workbooks("AAV Daily Reports 2010_ver1.xls").ActiveSheet '"" CLOSE THE BO DOWNLOAD DATA FILE ""' Workbooks(BO_Datafile_Name).Activate Workbooks(BO_Datafile_Name).Close Workbooks("AAV Daily Reports 2010_ver1.xls").Worksheets("Sheet1").Activate BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count Set BOReportWS = Worksheets("BO Download") ThisWorkbook.Worksheets("Sheet1").Range("A4:AW" & BOReport_lastRow).Copy ThisWorkbook.Worksheets("BO Download").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("B4").Select ThisWorkbook.Worksheets("Sheet1").Delete BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row BOPos = InStr(1, BOReportWS.Range("IV3").End(xlToLeft).Address(Colu mnAbsolute:=False), "$", vbTextCompare) BOReport_lastColumn = Left(BOReportWS.Range("IV3").End(xlToLeft).Address (ColumnAbsolute:=False), BOPos - 1) Unload Me Worksheets("BO Download").Select Range("A2") = "This Report was generated on " & Format(Now, "mmmm d, yyyy h:mm:ss AMPM") & " (Eastern Time)" Range("B4").Select Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate '"" DELETE BO DOWNLOAD TAB, SAVE THE FILE AND CALCULATE THE WORKSHEET ""' Dim obj As OLEObject Dim filename As String, todaysDate As Date filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) 'Worksheets("Goals Tracker").Calculate Worksheets("Goals Tracker").Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Worksheets("BO Download").Select Worksheets("BO Download").Delete ActiveWorkbook.SaveAs filename & "_" & Format(Date, "mmmdd_yy") & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic End Sub "Ryan H" wrote: There's no need to select the worksheet in order to do the calculation. Does the range you reference have a ton of functions in it that refer to macros? Do you have other macros firing every time a cell in calculated? For example, if you have code in the worksheet Change or Calculate Events it could take a while. This may help a little. Application.ScreenUpdating = False Worksheets("Goals Tracker").Range("G3:GA712").Calculate Application.ScreenUpdating = True Can you post the entire procedure and any other macros that are in the workbook? It would be more helpful. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Ayo" wrote: I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet().Range.Calculate Problem
I have Workbook.Open and BeforeClose for the workbook level Modules. The
formulas in Worksheets("Goals Tracker").Range("G3:GA712") are pretty much all SumProduct formula with data derived from another sheet which contains only data and nothing else. I know that there is nothing crazy in the code, the only problem is that is takes forever to complete the Worksheet.Range.calculate line. When I tried: Worksheets("Goals Tracker").Range("G3:GA12"), it took about a minute to complete the calculation. Then I tried: Worksheets("Goals Tracker").Range("G3:GA222"), and it takes more than the 22 minutes I was expecting. As of right now it is still running at 25 mins and counting "Ryan H" wrote: I don't see anything crazy in the code you posted that would be causing this issue. Is there any macros in the "Goals Tracker" worksheet module? Is so, can you post those codes? Do the formulas or values in range Worksheets("Goals Tracker").Range("G3:GA712") linked to other workbooks? If so, do those workbooks have worksheet macros? What about the Workbook Level Modules? Any macros in the SheetCalculate or SheetChange Events? -- Cheers, Ryan "Ayo" wrote: There are lots of macros in the work book. I will have to send you the files. But the only that is causing the problem is inside a Sub for a button on a Userform: Private Sub cmdUpdate_Click() Dim ws As Worksheet, c As Range Dim BO_Datafile_Name As String, BOReport_lastColumn As String Dim BOReport_lastRow As Integer, BOPos As Integer Dim BOReportWS As Worksheet, goalsTrackerWS As Worksheet Dim rngBOReport As Range Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Workbooks.Open filename:=Me.txtFilePath.Value, UpdateLinks:=0, ReadOnly:=True BO_Datafile_Name = ActiveWorkbook.Name Workbooks(BO_Datafile_Name).Worksheets("Sheet1").S elect Workbooks(BO_Datafile_Name).Worksheets("Sheet1").C opy Befo=Workbooks("AAV Daily Reports 2010_ver1.xls").ActiveSheet '"" CLOSE THE BO DOWNLOAD DATA FILE ""' Workbooks(BO_Datafile_Name).Activate Workbooks(BO_Datafile_Name).Close Workbooks("AAV Daily Reports 2010_ver1.xls").Worksheets("Sheet1").Activate BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count Set BOReportWS = Worksheets("BO Download") ThisWorkbook.Worksheets("Sheet1").Range("A4:AW" & BOReport_lastRow).Copy ThisWorkbook.Worksheets("BO Download").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("B4").Select ThisWorkbook.Worksheets("Sheet1").Delete BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row BOPos = InStr(1, BOReportWS.Range("IV3").End(xlToLeft).Address(Colu mnAbsolute:=False), "$", vbTextCompare) BOReport_lastColumn = Left(BOReportWS.Range("IV3").End(xlToLeft).Address (ColumnAbsolute:=False), BOPos - 1) Unload Me Worksheets("BO Download").Select Range("A2") = "This Report was generated on " & Format(Now, "mmmm d, yyyy h:mm:ss AMPM") & " (Eastern Time)" Range("B4").Select Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate '"" DELETE BO DOWNLOAD TAB, SAVE THE FILE AND CALCULATE THE WORKSHEET ""' Dim obj As OLEObject Dim filename As String, todaysDate As Date filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) 'Worksheets("Goals Tracker").Calculate Worksheets("Goals Tracker").Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Worksheets("BO Download").Select Worksheets("BO Download").Delete ActiveWorkbook.SaveAs filename & "_" & Format(Date, "mmmdd_yy") & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic End Sub "Ryan H" wrote: There's no need to select the worksheet in order to do the calculation. Does the range you reference have a ton of functions in it that refer to macros? Do you have other macros firing every time a cell in calculated? For example, if you have code in the worksheet Change or Calculate Events it could take a while. This may help a little. Application.ScreenUpdating = False Worksheets("Goals Tracker").Range("G3:GA712").Calculate Application.ScreenUpdating = True Can you post the entire procedure and any other macros that are in the workbook? It would be more helpful. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Ayo" wrote: I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet().Range.Calculate Problem
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet().Range.Calculate Problem
|
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I apologize, but I do not think I am going to be able to help youon this.
Ayo
This still an issue? I'd be interested in digging into it. If you would like me to, what type of CPU(s) in the system? How much memory? What is the average number and size of the arrays in the SumProduct calculations? What version of Excel? How much data on other sheets? Mail me a copy of xls? It is possible this is a capacity of your system issue. you quote 1 minute for 12 rows with less than 200 columns and that would be 2400/60=40 -- sumproducts per second... I did a test with 1000 rows x 200 columns; each with a sumproduct of 2 arrays averaging 1000 rows and 100 columns each. This took about a minute or 100000/60 = 1600+ per second. I don't believe that there will be 40x difference in CPU power between your and my system. Therefore, I am leaning on your application being so large that it is paging (mine is .5GB and it doesn't have other datasheets). David On Monday, February 22, 2010 12:27 PM Ayo wrote: I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I cannot use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. On Monday, February 22, 2010 12:39 PM Ryan H wrote: There is no need to select the worksheet in order to do the calculation. Does the range you reference have a ton of functions in it that refer to macros? Do you have other macros firing every time a cell in calculated? For example, if you have code in the worksheet Change or Calculate Events it could take a while. This may help a little. Application.ScreenUpdating = False Worksheets("Goals Tracker").Range("G3:GA712").Calculate Application.ScreenUpdating = True Can you post the entire procedure and any other macros that are in the workbook? It would be more helpful. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Ayo" wrote: On Monday, February 22, 2010 12:58 PM Ayo wrote: There are lots of macros in the work book. I will have to send you the files. But the only that is causing the problem is inside a Sub for a button on a Userform: Private Sub cmdUpdate_Click() Dim ws As Worksheet, c As Range Dim BO_Datafile_Name As String, BOReport_lastColumn As String Dim BOReport_lastRow As Integer, BOPos As Integer Dim BOReportWS As Worksheet, goalsTrackerWS As Worksheet Dim rngBOReport As Range Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Workbooks.Open filename:=Me.txtFilePath.Value, UpdateLinks:=0, ReadOnly:=True BO_Datafile_Name = ActiveWorkbook.Name Workbooks(BO_Datafile_Name).Worksheets("Sheet1").S elect Workbooks(BO_Datafile_Name).Worksheets("Sheet1").C opy Befo=Workbooks("AAV Daily Reports 2010_ver1.xls").ActiveSheet '"" CLOSE THE BO DOWNLOAD DATA FILE ""' Workbooks(BO_Datafile_Name).Activate Workbooks(BO_Datafile_Name).Close Workbooks("AAV Daily Reports 2010_ver1.xls").Worksheets("Sheet1").Activate BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count Set BOReportWS = Worksheets("BO Download") ThisWorkbook.Worksheets("Sheet1").Range("A4:AW" & BOReport_lastRow).Copy ThisWorkbook.Worksheets("BO Download").Select Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("B4").Select ThisWorkbook.Worksheets("Sheet1").Delete BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row BOPos = InStr(1, BOReportWS.Range("IV3").End(xlToLeft).Address(Colu mnAbsolute:=False), "$", vbTextCompare) BOReport_lastColumn = Left(BOReportWS.Range("IV3").End(xlToLeft).Address (ColumnAbsolute:=False), BOPos - 1) Unload Me Worksheets("BO Download").Select Range("A2") = "This Report was generated on " & Format(Now, "mmmm d, yyyy h:mm:ss AMPM") & " (Eastern Time)" Range("B4").Select Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate '"" DELETE BO DOWNLOAD TAB, SAVE THE FILE AND CALCULATE THE WORKSHEET ""' Dim obj As OLEObject Dim filename As String, todaysDate As Date filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) 'Worksheets("Goals Tracker").Calculate Worksheets("Goals Tracker").Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Worksheets("BO Download").Select Worksheets("BO Download").Delete ActiveWorkbook.SaveAs filename & "_" & Format(Date, "mmmdd_yy") & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic End Sub "Ryan H" wrote: On Monday, February 22, 2010 1:29 PM Ryan H wrote: I do not see anything crazy in the code you posted that would be causing this issue. Is there any macros in the "Goals Tracker" worksheet module? Is so, can you post those codes? Do the formulas or values in range Worksheets("Goals Tracker").Range("G3:GA712") linked to other workbooks? If so, do those workbooks have worksheet macros? What about the Workbook Level Modules? Any macros in the SheetCalculate or SheetChange Events? -- Cheers, Ryan "Ayo" wrote: On Monday, February 22, 2010 1:43 PM Ayo wrote: I have Workbook.Open and BeforeClose for the workbook level Modules. The formulas in Worksheets("Goals Tracker").Range("G3:GA712") are pretty much all SumProduct formula with data derived from another sheet which contains only data and nothing else. I know that there is nothing crazy in the code, the only problem is that is takes forever to complete the Worksheet.Range.calculate line. When I tried: Worksheets("Goals Tracker").Range("G3:GA12"), it took about a minute to complete the calculation. Then I tried: Worksheets("Goals Tracker").Range("G3:GA222"), and it takes more than the 22 minutes I was expecting. As of right now it is still running at 25 mins and counting "Ryan H" wrote: On Monday, February 22, 2010 1:49 PM Ryan H wrote: Is it possible to send me a copy of the workbook so I can try to duplicate the problem? I can look at it and try to make a suggestion. -- Cheers, Ryan "Ayo" wrote: On Monday, February 22, 2010 2:07 PM Ayo wrote: Thanks Ryan. I just sent it over. "Ryan H" wrote: On Monday, February 22, 2010 2:41 PM Ryan H wrote: I apologize, but I do not think I am going to be able to help you on this. I believe you simply have too much data for Excel to calculate at once, but I do not know that for sure. I would encourage you to Google "Why does Excel calculate very slow?" I found lots of help, but simply do not have time to explore the issue. Sorry I could have been more helpful. If you do find a solution, send me an e-mail of the fix. I'd be interested in knowing. Good Luck! -- Cheers, Ryan "Ayo" wrote: |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| problem building Function with worksheet name and range | Excel Programming | |||
| Sort Worksheet Range from another worksheet range, Excel 2000 & 2003 | Excel Programming | |||
| Can I calculate just one worksheet in a multi-worksheet workbook? | Excel Discussion (Misc queries) | |||
| Formula to retrieve range of dates from a worksheet to calculate d | Excel Worksheet Functions | |||
| Findjjing the range that triggered worksheet calculate | Excel Programming | |||
Linear Mode
