Microsoft Excel - Excel VBA deleting rows ten times slower in Excel2010 compared to 2007

Asked By Stef Kicken on 03-Oct-12 09:51 AM
Issue
I recently changes the version of MS Excel from 2007 to 2010. I use a macro to delete specific content (rows / ranges) in a spreadsheet. The macro runs about ten times as slow using the 2010 version of MS Excel.

Example of code used
Rows("10:10").select
Selection.Delete Shift:=xlUP
or
Range(Cells(2,3),Cells(10,50)).delete shift:=xlup

Tried so far
Disable COM add-ins
Record the event of deleting a line using the macro recorder -> results in same VBA code used (.delete shift:=xlup).
Also when manually deleting a row - without VBA - it is much slower
I'm an experience user of Excel VBA. 

Hope anybody has a experience with this issue.

Stef

wally eye replied to Stef Kicken on 03-Oct-12 10:29 AM
You could turn calculations and screen updating off:

dim intCalc    as integer

intcalc = application.calculation
application.calculation = xlmanual
application.screenupdating = false
'
'  Your code here
'
application.calculation = intcalc
application.screenupdating = true

I would stay away from the Select version of your code, it just adds overhead.  Also, you can delete multiple rows at once:

Range("8:8,10:10").delete

Normally I build a string to contain the ranges to delete, then delete them all at once.  The only caveat there is that the string cannot exceed 256 characters, so I put a check in if it is > 240 or so, perform the delete and reset the string.  Of course, in that case you would want to start at the bottom of the sheet and work your way up.
Stef Kicken replied to wally eye on 04-Oct-12 02:23 AM
In the workbook I set the calculation mode to manual mode and control calculation mode via VBA. This allows me to control exactly what is re-calculated to save calculation time (for example only a certain sheet or a selection of sheets). So putting the calculation to automatic per default is no option.
The workbook contains a lot of sheets and functionality, putting the automatic calculation means waiting for 5 to 10 seconds before the entire workbook is recalculated. Re-design of the entire workbook is not an option at this moment in time.

Turning off screenupdating already done.

Thanks for the code to delete multiple rows/ranges at once. That will help.

Still this is a workaround for the mentioned issue, there must be a better solution.
Stef Kicken replied to wally eye on 05-Oct-12 10:43 AM
Did also some tests here. First selecting multiple ranges at onces takes of only a bit of the required time (10/20%, that's all). I also tried to move ranges instead, this also doesn't help.

When I insert a new sheet in the workbook and delete some rows, it also takes about 10 seconds to delete. It seems that the entire workbook rebuild, while my calculation setting is still on manual calculation.
wally eye replied to Stef Kicken on 09-Oct-12 06:53 PM
Are you using volatile functions?  Such as Indirect, Today, Now, Offset?  You can do some searching on Excel volatile functions, they can trigger recalculations you wouldn't expect.  I had the Today() function bite me the other day, it was causing data updates everytime anything changed on the spreadsheet, slowing things down considerably.  I changed the field to be updated from VBA, where I could control it, sped up the process considerably.