在Excel中启用WrapText时，运行ClearContents的性能非常慢

I'm debugging the following code in an Excel 2007 VBA, which seems very simple. For some reason its taking about 30 minutes to process 900 rows of data. I think I have narrowed it down to some cell formatting, specifically the WrapText option. Is there something I'm missing here that can increase the performance when deleting these rows.

我正在Excel 2007 VBA中调试以下代码，这看起来非常简单。由于某种原因，它需要大约30分钟来处理900行数据。我想我已经缩小到一些单元格格式，特别是WrapText选项。我在这里缺少一些可以在删除这些行时提高性能的东西。

Sub ClearContentsOfActive()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim NumLines
    NumLines = ActiveSheet.UsedRange.Rows.Count

    Range("2:" & NumLines + 3).Select
    Selection.ClearContents
    Selection.Delete Shift:=xlUp

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Additionally, The user was not experiencing these delays in Excel 2003, with the same code. Thanks in advance

此外，用户在Excel 2003中没有遇到这些延迟，使用相同的代码。提前致谢

Update

In response to the redundancy of the ClearContents line, this is the original code that was working in Excel 2003, thats why I left it. Commenting this line was one of the first things I tried, as I agreed that it may've been redundant. See below for my psuedo perfomance metrics.

为了响应ClearContents行的冗余，这是在Excel 2003中工作的原始代码，这就是我离开它的原因。评论这一行是我尝试的第一件事，因为我同意它可能是多余的。请参阅下面的psuedo性能指标。

Test1

测试1

Selection.ClearContents  ''//  Takes ~30 mins
Selection.Delete Shift:=xlUp  ''// then this takes <1min

Test2

TEST2

'Selection.ClearContents  '// If this line is commented
Selection.Delete Shift:=xlUp  ''// then this line takes ~30 mins

The reason I believe it has something to do with the WrapText feature is because when you clear the line of WrapText it performs and AutoFit operation on the row. I thought that hiding screen updates or disabling events would help.

我认为它与WrapText功能有关的原因是因为当您清除WrapText行时，它会对该行执行并执行AutoFit操作。我认为隐藏屏幕更新或禁用事件会有所帮助。

2 个解决方案

#1

Is it actually necessary to call .ClearContents before calling .Delete? It seems to me that the call to .ClearContents is redundant. You're emptying the values in a range of cells that you simply remove from existence in the very next line.

实际上是否有必要在调用.Delete之前调用.ClearContents？在我看来，对.ClearContents的调用是多余的。您将清空一系列单元格中的值，这些单元格在下一行中只是从存在中删除。

I don't know if that will help with the performance issue, but it is worth a shot.

我不知道这是否有助于解决性能问题，但值得一试。

edit: In response to your update, how about avoiding the selction, and just doing the following:

编辑：为了响应您的更新，如何避免选择，并执行以下操作：

Range("2:" & NumLines + 3).Delete shift:=xlUp

You can also try to get rid of the text wrapping ahead of time:

您还可以尝试提前删除文本包装：

Range("2:" & NumLines + 3).WrapText = False
Range("2:" & NumLines + 3).Delete shift:=xlUp

#2

So it seems to be related to the ColorIndex property of the cells and not the WrapText Feature. The Macro for the page had the following code

所以它似乎与单元格的ColorIndex属性有关，而与WrapText功能无关。该页面的宏具有以下代码

For r = 0 to 1000
  For c = 0 to 15
    Sheets.Cells(r, c).ColorIndex = 14
  Next c
Next r

I recommended that the user uses ConditionalFormatting, junking the macro all together and that fixed his performance issues.

我建议用户使用ConditionalFormatting，将宏汇集在一起，这样就解决了他的性能问题。

ConditionalFormatting was based on a formula on the entire sheet that turned the yellow if the following evaluated to true

ConditionalFormatting基于整个工作表上的公式，如果以下评估为true，则该公式将变为黄色

=IF(ROW()>10, FALSE, LEN($A1)>0)

#1