I have a macro that is cycling through a collection that then utilizes selected members of that collection to flag selected excel rows for deletion. The coding seems to be relatively simple but the issue is this:
A.) There are approximately 2000 lines that it has to delete.
B.) On 2 of the office computers it takes approximately 20-30 seconds.
C.) On 2 other office computers, it takes approximately 90 minutes
***The behavior is completely replicatable, and happens 100% of the time. The fast PC's are always fast, and the slow PC's are always slow.
The code is below, but the following steps have already been taken:
1.) Screen updating is turned off
2.) Calculation is turned off
3.) All other files are closed when this file runs
The PC's are almost all identical (Dell Laptops, same models, approx 1 year old, 2 gig of RAM), utilizing Windows XP and Excel 2003 SP4(?). There is no difference in addins or reference libraries between the PC's, and no significant difference in installed software. There are also no noticeable drains in the task manager or processes hitting the CPU, besides Excel. The CPU is topping out at approx 55% on the slow running PC's. Not sure on the fast running ones.
Here is the code:
Dim Cell As Range
Dim DetailRows As New Collection
Dim TotalRows As New Collection
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Cell In g_rng_CurRpt_DetailRows '**** This is the collection that defines the range to be scanned for the "1" flag
If Cell.Value = 1 Then DetailRows.Add Cell
For Each Cell In DetailRows
Cell.EntireRow.Delete '****** THIS IS THE PROBLEM LINE *******
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Basically on the fast preforming PC's the problem line is instantaneous, on the slow performing PC's the problem line takes approx 5 seconds to execute. Multiply that 5 second execution time by 2000 executions and I've got a problem.
The only other piece of information is that I was able to speed execution up significantly, by hardcoding the formulas prior to line deletion. Theoretically this should make no difference as the calculation was turned off, but it did. Still not as fast as the 2 faster performing PC's, but performance went from 90 minutes to 3-4 minutes. Its almost as if its still calcing on the 2 slow ones and thats whats holding me up. I say almost, because the formulas in the cells are mostly DBRW formulas linking to Cognos TM1, and the data is not changing, so it appears that I'm not actually getting any new data, or calculations in those cells, while the process is running.
IMO, its almost as if there is a setting somewhere that I'm missing. If anyone has any idea what might be causing the slow execution of the exact same code, in these 2 PC's, I would greatly appreciate it.