Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling taking long time to complete
I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a
macro, I sub total columns 7 and 17. I use a macro to copy select cells into a second sheet. After this, I remove the subtotals. I use lookups from the second sheet into the first sheet. As part of my tests, I have since copied and pasted the lookup cells as absolute values. There are no longer any lookups in any sheet. The initial subtotal and it's removal, when creating the macro, took seconds to complete, however on subsequent attempts, they take hours. I have since copied and pasted the sheet into a different workbook, and the subtotalling function in the second workbook takes seconds to complete. What can I do to improve the performance? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling taking long time to complete
Instead of using the Data | Subtotals routine, for which your data
must be sorted beforehand, and then copying the values to another sheet, you can set up some SUMIF formulae in the second sheet to give you the same as the subtotals you currently have. Just list the values of the field that you want the subtotal for in column A, and then a formula like: =SUMIF(Sheet1!ref_col,A1,Sheet1!G:G) and: =SUMIF(Sheet1!ref_col,A1,Sheet1!Q:Q) will give you the equivalent, where ref_col will be the column you generate the subtotals from, eg C:C. Copy these down as required. Hope this helps. Pete On Jul 14, 10:43*am, AndyV wrote: I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a macro, I sub total columns 7 and 17. I use a macro to copy select cells into a second sheet. After this, I remove the subtotals. I use lookups from the second sheet into the first sheet. As part of my tests, I have since copied and pasted the lookup cells as absolute values. There are no longer any lookups in any sheet. The initial subtotal and it's removal, when creating the macro, took seconds to complete, however on subsequent attempts, they take hours. I have since copied and pasted the sheet into a different workbook, and the subtotalling function in the second workbook takes seconds to complete. What can I do to improve the performance? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling taking long time to complete
Thanks Pete,
The problem is that I'm using both the sub-total values and the rows on which they appear to determine what data I need to copy and manipulate. I thought there may be some issue regarding undo. I've read an MS forum where the undo command is maintained both within macros and when the file is saved, bBut that it can be cleared from within a macro. I thought Excel may have a large undo list of actions that it was trying to maintain. "Pete_UK" wrote: Instead of using the Data | Subtotals routine, for which your data must be sorted beforehand, and then copying the values to another sheet, you can set up some SUMIF formulae in the second sheet to give you the same as the subtotals you currently have. Just list the values of the field that you want the subtotal for in column A, and then a formula like: =SUMIF(Sheet1!ref_col,A1,Sheet1!G:G) and: =SUMIF(Sheet1!ref_col,A1,Sheet1!Q:Q) will give you the equivalent, where ref_col will be the column you generate the subtotals from, eg C:C. Copy these down as required. Hope this helps. Pete On Jul 14, 10:43 am, AndyV wrote: I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a macro, I sub total columns 7 and 17. I use a macro to copy select cells into a second sheet. After this, I remove the subtotals. I use lookups from the second sheet into the first sheet. As part of my tests, I have since copied and pasted the lookup cells as absolute values. There are no longer any lookups in any sheet. The initial subtotal and it's removal, when creating the macro, took seconds to complete, however on subsequent attempts, they take hours. I have since copied and pasted the sheet into a different workbook, and the subtotalling function in the second workbook takes seconds to complete. What can I do to improve the performance? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling taking long time to complete
If you're copying the subtotal rows (not the details), then I bet that creating
a pivottable would be quicker than subtotals, copy|pasting, removing subtotals. But if you want to use subtotals (I wouldn't!)... I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. AndyV wrote: I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a macro, I sub total columns 7 and 17. I use a macro to copy select cells into a second sheet. After this, I remove the subtotals. I use lookups from the second sheet into the first sheet. As part of my tests, I have since copied and pasted the lookup cells as absolute values. There are no longer any lookups in any sheet. The initial subtotal and it's removal, when creating the macro, took seconds to complete, however on subsequent attempts, they take hours. I have since copied and pasted the sheet into a different workbook, and the subtotalling function in the second workbook takes seconds to complete. What can I do to improve the performance? -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling taking long time to complete
Thanks Dave,
I now appreciate that subtotals seem system intensive and there are better ways but all my code in this sheet is based around them. I'll add your lines to improve the general performance. Its messy to explain what I'm doing, but I've created subtotals above the detail rows which I compare to a cell in the first of the subsequent details rows (which contains what should be the correct total). If there is a difference, I add this difference to the first detail line, then move on down to the next sub total line. At the end of this process, the total of the details matches the total that is held on the detail line. (I'm trying to get around some vat rounding issues on a data export). I'm using excel 2007, but ported part of the code using compatibility mode and subtotals take minutes, so it seems to be a problem with 2007. I've decided to try and create and manipulate the data by creating a new workbook on every occasion I create an export file. Andrew "Dave Peterson" wrote: If you're copying the subtotal rows (not the details), then I bet that creating a pivottable would be quicker than subtotals, copy|pasting, removing subtotals. But if you want to use subtotals (I wouldn't!)... I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. When/if you're hiding rows/columns, excel wants to figure out where to draw those lines each time you change the layout. AndyV wrote: I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a macro, I sub total columns 7 and 17. I use a macro to copy select cells into a second sheet. After this, I remove the subtotals. I use lookups from the second sheet into the first sheet. As part of my tests, I have since copied and pasted the lookup cells as absolute values. There are no longer any lookups in any sheet. The initial subtotal and it's removal, when creating the macro, took seconds to complete, however on subsequent attempts, they take hours. I have since copied and pasted the sheet into a different workbook, and the subtotalling function in the second workbook takes seconds to complete. What can I do to improve the performance? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
taking a long list of duplicates... | Excel Discussion (Misc queries) | |||
Taking long time to save file to sub folder | Excel Discussion (Misc queries) | |||
Excel Taking Long TIme to Start | Excel Discussion (Misc queries) | |||
External Links Taking too long | Excel Discussion (Misc queries) | |||
how to stop emails incoming its taking a long time to download | New Users to Excel |