Column/Row operations (insert, delete) are very sl

问题:

I am having great problems with a VBA macro I've written for Excel 2003. It imports some data from other files. The result is that the file size of the Excel file (number of Rows/Cols) increases with every imported file. In Excel 2003 it runs very fast for the first 50 imported files and then it slows down. But with Excel 2010 it takes a considerable amount of time even for small numbers of files. The problem lies in one statement that is executed many times:

Sheets("Sheetname").Rows(LastRow).Insert Shift:=xlDown

This line takes nearly a second in Excel 2010 when the file is becoming larger. When I add a Row manually I am experiencing the same problem.

I do the usual bunch of things to improve performance:

Application.ScreenUpdating = False  
Application.DisplayStatusBar = False  
Application.Calculation = xlCalculationManual  
Application.EnableEvents = False  
ActiveSheet.DisplayPageBreaks = False  
ActiveSheet.AutoFilterMode = False

Thanks in advance for your help. I am pretty sure that someone else has already had this problem.

回答1:

The problem was that I applied conditional formatting. For every new line that was added to the report a new conditional format rule was created to color a cell in one column that was the same for every row (e.g. the format rule was "if the cell in column A of the current row contains 'enabled' then make the cell green").
In Excel 2003 the conditional formats don't seem to be recalculated if Application.Calculation is set to manual or if the conditional formats are handled differently (I don't know).
Knowing this I manually created a conditional format rule for the whole columns that had to be colored and removed the code to color the single cells from the spreadsheet. Now the thing runs fast in Excel 2010.
I wonder if there is a possibility to file a bug report (disable conditional format recomputing if Application.Calculation is set to manual).

回答2:

Using Insert is slow because it forces Excel to do a lot of work. Its much faster to just write a large chunk of data directly from a variant to the sheet.
If you want to preserve some formulas at the bottom such as totals then it would be faster to copy the formula block into a variant, write the data to the sheet, adjust the formula and then write it back at the end.
If you have other formulae that reference the sheet where the data is added you could use dynamic range names to refer to the data, or ordinary range names to refer to the total formulae.

回答3:

A bit late to the party, but first import all of the data, then add formatting/conditional formatting for a range vs. a new conditional formatting per record.

回答4:

Very strange.... Inserting a row was fast. Then, I sorted a range of rows, and it became excruciatingly slow. I deleted the row after UsedRange, and it was fine again!