Help with optimization

  • Thread starter Thread starter theSquirrel
  • Start date Start date
T

theSquirrel

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
 
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.
 
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
Click to expand...

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
 
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
 
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
 
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
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top