Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Issues
Bob & Frank,
Many thanks for your replies. I thought that would do it! What it did do is make the first run 7 seconds instead of 30.....but when I run it immediately after the first run, it takes 6 minutes again. Is there memeory being clogged somewhere by the first run that I need to clear?? Thanks again!! -Steph Steph, Try setting the Application calculation property to xlCalculationManual before the code, and reset to xlCalculationAutomatic after. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message om... Hello. I am running some code that clears a range of cells on multiple sheets, then populates the same sheets with data being pulled from a database-like worksheet. My question is this: Sometimes when I run the code, it takes 20 seconds. Majority of the time I run the code it takes 6 minutes! IS there data in a cache or something that is forcing things to run so slowly? Usually, when I first open the file and the sheets are already blank, it runs quickly. If I run it again when the sheets are already populated, it takes forever! So I thought it was just the sheer size of the file after being populated that slowed things. BUT, then I selected every sheet, and hit the home key. And when I ran the code, it ran in 20 seconds! I am SO confused!! Anyone have any ideas on how to get the code to consistently run quickly? The code is below. Thanks so much!! Sub Run_Forecast() Clear Pop_Forecast End Sub Sub Clear() Dim sh As Worksheet Application.ScreenUpdating = False clrarray = Array("Forecast", "LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup") For Each sh In ActiveWorkbook.Worksheets(clrarray) ' Sheets("SMLC Cab").Range("A1").Select ' sh.Range("A1").Select sh.Range("A5:EC500").ClearContents Next sh Application.ScreenUpdating = True End Sub Sub Pop_Forecast() Dim shtarray As Sheets Dim frng As Range Dim sh As Worksheet Application.ScreenUpdating = False Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate 'Copy Formula Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.Select Selection.PasteSpecial Paste:=xlPasteFormulas 'Consolidate2 Forecast.Range("A5").Consolidate Sources:=Array( _ "'LMU'!R5C5:R500C133" _ , "'Kit'!R5C5:R500C133" _ , "'SMLC'!R5C5:R500C133" _ , "'WLG'!R5C5:R500C133" _ , "'SMLC Cab'!R5C5:R500C133" _ , "'Serv Cab'!R5C5:R500C133" _ , "'Ntwk Kit'!R5C5:R500C133" _ , "'TDAX'!R5C5:R500C133" _ , "'EMS'!R5C5:R500C133" _ , "'SCOUT'!R5C5:R500C133" _ , "'Dir Coup'!R5C5:R500C133" _ ), Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False Forecast.Activate Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Issues
Steph,
You could try a few other things a) clear the objects down at the end Set shtArray = Nothing Set frng = Nothing Set sh = N othing b) reduce the selecting Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Other than that, it is probably in the Consolidate, but I can't help with that as I don't use it and know nothing about it. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message om... Bob & Frank, Many thanks for your replies. I thought that would do it! What it did do is make the first run 7 seconds instead of 30.....but when I run it immediately after the first run, it takes 6 minutes again. Is there memeory being clogged somewhere by the first run that I need to clear?? Thanks again!! -Steph Steph, Try setting the Application calculation property to xlCalculationManual before the code, and reset to xlCalculationAutomatic after. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm having formula issues | Excel Discussion (Misc queries) | |||
Taskbar Issues | Setting up and Configuration of Excel | |||
Time Issues | Excel Worksheet Functions | |||
Printing Issues | Excel Discussion (Misc queries) | |||
Connection issues | Excel Programming |