Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal in samll file take a lot of time (VBA)
Hi,
I have big xls file. I run macro on this file and in the macro I add new workbook by: Set NewBook = Workbooks.Add In the new workbook I add data (between 5 to 100 rows) and the create subtotal in the new workbook: newWB.Activate newWB.Sheets(1).Activate ActiveCell.CurrentRegion.Select Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True It take a lot of time to the macro for run Subtotal (look like it calculate the big file that take many times). How can I do the subtotal would not calculate the big file (Application.Calculation = xlManual) (When I make manually the subtotal on the new workbook it take a second) Thanks, Shlomit |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal in samll file take a lot of time (VBA)
Just a guess...
After you add your 5 to 100 rows, stop your macro. Check to see where the last used cell is (hit ctrl-end). Maybe you're subtotalling more data than you think -- up to a million rows in xl2007! (I don't see anything that looks like it would take a long time in your code.) Shlomit wrote: Hi, I have big xls file. I run macro on this file and in the macro I add new workbook by: Set NewBook = Workbooks.Add In the new workbook I add data (between 5 to 100 rows) and the create subtotal in the new workbook: newWB.Activate newWB.Sheets(1).Activate ActiveCell.CurrentRegion.Select Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True It take a lot of time to the macro for run Subtotal (look like it calculate the big file that take many times). How can I do the subtotal would not calculate the big file (Application.Calculation = xlManual) (When I make manually the subtotal on the new workbook it take a second) Thanks, Shlomit -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
A list of data with time stamps, how subtotal at 10 min intervals | Excel Worksheet Functions | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Run-time changes to subtotal parameter list | Excel Programming |