Excel 2010中的列/行操作（插入，删除）非常慢 [英] Column/Row operations (insert, delete) are very slow in Excel 2010

问题描述

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

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

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

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.

推荐答案

问题是我应用条件格式化。对于添加到报表中的每一条新行，都创建了一个新的条件格式规则，用于为每行中相同的列列中的单元格进行着色（例如，格式规则为如果当前行的列A中的单元格包含启用然后使单元格为绿色）。

在Excel 2003中，如果将Application.Calculation设置为手动或条件格式处理不同，则条件格式似乎不重新计算（我不知道）

知道这一点，我手动创建了一个必须被着色的整个列的条件格式规则，并从代码中删除了电子表格中的单个单元格。现在在Excel 2010中运行得很快。

我想知道是否有可能提交错误报告（如果Application.Calculation设置为手动，则禁用条件格式重新计算）。

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

这篇关于Excel 2010中的列/行操作（插入，删除）非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！