vba Excel 2010 中的列/行操作（插入、删除）非常慢

Column/Row operations (insert, delete) are very slow in Excel 2010

提问by Shirky

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:

我为 Excel 2003 编写的 VBA 宏遇到了很大问题。它从其他文件导入了一些数据。结果是 Excel 文件的文件大小（行数/列数）随着每个导入文件的增加而增加。在 Excel 2003 中，前 50 个导入文件的运行速度非常快，然后会变慢。但是对于 Excel 2010，即使是少量文件也需要相当长的时间。问题在于一条语句执行了多次：

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.

当文件变大时，此行在 Excel 2010 中需要将近一秒钟。当我手动添加一行时，我遇到了同样的问题。

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.

在此先感谢您的帮助。我很确定其他人已经遇到了这个问题。

回答by Shirky

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).

问题是我应用了条件格式。对于添加到报告中的每一行，都会创建一个新的条件格式规则来为每一行相同的列中的单元格着色（例如，格式规则是“如果当前行的 A 列中的单元格包含 '启用'然后将单元格设为绿色")。
在 Excel 2003 中，如果 Application.Calculation 设置为手动或条件格式的处理方式不同（我不知道），则似乎不会重新计算条件格式。
知道这一点后，我手动为必须着色的整列创建了条件格式规则，并删除了为电子表格中的单个单元格着色的代码。现在这件事在 Excel 2010 中运行得很快。
我想知道是否有可能提交错误报告（如果 Application.Calculation 设置为手动，则禁用条件格式重新计算）。

回答by Charles Williams

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.

使用 Insert 很慢，因为它迫使 Excel 做很多工作。将大量数据直接从变体写入工作表要快得多。
如果您想在底部保留一些公式，例如总计，那么将公式块复制到变体中，将数据写入工作表，调整公式，然后在最后将其写回会更快。
如果您有其他公式引用添加数据的工作表，您可以使用动态范围名称来引用数据，或使用普通范围名称来引用总公式。

回答by Jason Erdmann

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.

聚会有点晚了，但首先导入所有数据，然后添加范围的格式/条件格式与每条记录的新条件格式。

回答by user2081126

Using Insert will slow up the process if we have conditional formats. So there are 2 options here. One to delete the conditional formats before running the insert code. Below code snippiet will help

如果我们有条件格式，使用 Insert 会减慢进程。所以这里有2个选择。一种在运行插入代码之前删除条件格式。下面的代码片段将有所帮助

Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete

Second option is instead of inserting Rows one by one in loop, get the count of rows that needs to be inserted and then execute that in one single line which would drastically reduce the time of single execution. Blow code snippet will help for that

第二种选择是不是在循环中一一插入行，而是获取需要插入的行数，然后在一行中执行该计数，这将大大减少单次执行的时间。Blow 代码片段将对此有所帮助

ActiveCell.EntireRow.Offset(1).Resize(rowCount).Insert Shift:=xlDown

Where rowCount is number of Rows to Insert.

其中 rowCount 是要插入的行数。

回答by N. Dean Meyer

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!

很奇怪......插入一行很快。然后，我对一系列行进行排序，结果变得极其缓慢。我删除了 UsedRange 之后的行，然后又好了！