vba 在 Excel 中启用 WrapText 时，运行 ClearContents 的性能非常低
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/678493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance running ClearContents is very slow when WrapText is enabled in Excel
提问by bendewey
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 中调试以下代码，这看起来很简单。出于某种原因，处理 900 行数据需要大约 30 分钟。我想我已经将它缩小到一些单元格格式，特别是 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 中工作的原始代码，这就是我离开它的原因。评论这条线是我尝试的第一件事，因为我同意它可能是多余的。请参阅下面的我的伪性能指标。
Test1
测试1
Selection.ClearContents ''// Takes ~30 mins
Selection.Delete Shift:=xlUp ''// then this takes <1min
Test2
测试2
'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 操作。我认为隐藏屏幕更新或禁用事件会有所帮助。
采纳答案by bendewey
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 Feature。该页面的宏具有以下代码
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 基于整个工作表上的一个公式，如果以下评估为真，该公式将变为黄色
=IF(ROW()>10, FALSE, LEN($A1)>0)
回答by e.James
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
