Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
I have some very simple code that takes forever to complete, below it
the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
Maybe you could just combine the ranges and clear once?
With TempList .Range("A20:K3019,M20:W3019,Y20:AC51").Value = "" end with (add as many addresses as you want while you're testing. theSquirrel wrote: I have some very simple code that takes forever to complete, below it the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
Maybe have a template worksheet that is populated with everything but what
you want to delete here. Then when you want to clear these ranges, just delete your worksheet and make a copy of your template worksheet to replace it. Steve "theSquirrel" wrote in message ... I have some very simple code that takes forever to complete, below it the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
On Apr 16, 5:34 pm, "Steve" wrote:
Maybe have a template worksheet that is populated with everything but what you want to delete here. Then when you want to clear these ranges, just delete your worksheet and make a copy of your template worksheet to replace it. Steve "theSquirrel" wrote in message ... I have some very simple code that takes forever to complete, below it the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel Dave, I tried your code above and added the 2 first sections together and unfortunately it took the same amount of time 3.2 seconds. Steve, I can't move the lists because the lists are in a hidden part of the only visible page in the workbook. I also can't move them because it would be a huge code overhaul that is not worth the 3.2 seconds i would be saving here. I am still open for suggestion, but I think I may have to live with this. theSquirrel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
maybe you're deleting formulas in the first 2 lines and they don't have to
recalc because they don't exist. did you try turning off screenupdating and setting calculation to manual before running the code? Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic -- Gary "theSquirrel" wrote in message ... I have some very simple code that takes forever to complete, below it the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
And maybe you have an event macro that's running for each change:
application.enableevents = false With TempList .Range("A20:K3019,M20:W3019,Y20:AC51").Value = "" end with application.enableevents = true theSquirrel wrote: On Apr 16, 5:34 pm, "Steve" wrote: Maybe have a template worksheet that is populated with everything but what you want to delete here. Then when you want to clear these ranges, just delete your worksheet and make a copy of your template worksheet to replace it. Steve "theSquirrel" wrote in message ... I have some very simple code that takes forever to complete, below it the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel Dave, I tried your code above and added the 2 first sections together and unfortunately it took the same amount of time 3.2 seconds. Steve, I can't move the lists because the lists are in a hidden part of the only visible page in the workbook. I also can't move them because it would be a huge code overhaul that is not worth the 3.2 seconds i would be saving here. I am still open for suggestion, but I think I may have to live with this. theSquirrel -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with optimization
On Apr 17, 5:33 am, Dave Peterson wrote:
And maybe you have an event macro that's running for each change: application.enableevents = false With TempList .Range("A20:K3019,M20:W3019,Y20:AC51").Value = "" end with application.enableevents = true theSquirrel wrote: On Apr 16, 5:34 pm, "Steve" wrote: Maybe have a template worksheet that is populated with everything but what you want to delete here. Then when you want to clear these ranges, just delete your worksheet and make a copy of your template worksheet to replace it. Steve "theSquirrel" wrote in message ... I have some very simple code that takes forever to complete, below it the sub: With TempList .Range("A20:K3019").Value = "" .Range("M20:W3019").Value = "" .Range("Y20:AC51").Value = "" .Range("AE20:AJ119").Value = "" .Range("AL20:AO518").Value = "" .Range("AQ20:AT518").Value = "" .Range("AV20:AV69").Value = "" .Range("AX20:BA519").Value = "" .Range("BC20:BD519").Value = "" .Range("BF20:BG519").Value = "" .Range("BI20:BJ519").Value = "" .Range("BL20:BM519").Value = "" .Range("BO20:BU519").Value = "" .Range("BW20:CC519").Value = "" .Range("CE20:CK51").Value = "" End With The first 2 lines of the delete the contents of a very large area of cells. Each of those lines takes 1.6 seconds, the rest take near no time. I have tried obviously '.Value = "" ' and '.ClearContents'. Both take the same amount of time. I have also tried turning calculation to manual before that work is done. I am wondering if there is anything I can do to make that work a bit faster. theSquirrel Dave, I tried your code above and added the 2 first sections together and unfortunately it took the same amount of time 3.2 seconds. Steve, I can't move the lists because the lists are in a hidden part of the only visible page in the workbook. I also can't move them because it would be a huge code overhaul that is not worth the 3.2 seconds i would be saving here. I am still open for suggestion, but I think I may have to live with this. theSquirrel -- Dave Peterson I don't know what events are causing this to happen, but adding the EnableEvents = False thing fixed the issue mostly, but adding: With Application .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ' do stuff With Application .EnableEvents = True .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With Fixed the issue entirely. Thanks guys. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Worksheet Functions | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Discussion (Misc queries) | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Setting up and Configuration of Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Links and Linking in Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Charts and Charting in Excel |