vba 使用 ListRows.Item(i).Delete 从 Excel 表中删除行时速度较慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4713035/
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
Slow when deleting rows from an Excel table using ListRows.Item(i).Delete
提问by Cylindric
I'm using VBA to populate an Excel "table" range from a subset of other data.
我正在使用 VBA 从其他数据的子集填充 Excel“表格”范围。
Getting the data is fast (either from a cached array or from a SQL query on data in another sheet), but the adding and deleting is SLOW.
获取数据很快（从缓存数组或从另一个工作表中的数据的 SQL 查询），但添加和删除很慢。
With Worksheets("Overview").ListObjects("OverviewServiceTable")
For i = .ListRows.Count To 1 Step -1
.ListRows(i).Delete
Next
For i = 0 To UBound(cache)
Set NewRow = .ListRows.Add(AlwaysInsert:=True)
NewRow.Range.Cells(1, 1).Value = cache(i)
Next
End With
Using a bit of basic profiling, it's taking up to a second per row to both add and delete rows. Is there a much faster way of clearing and updating a table like this? Otherwise I'll resort to using "plain" cell ranges formatted to look pretty, but then nice things like the Autofilters etc will go away.
使用一些基本的分析，每行最多需要一秒钟来添加和删除行。有没有更快的方法来清除和更新这样的表格？否则，我将求助于使用格式化为看起来很漂亮的“普通”单元格范围，但是像自动过滤器等这样的好东西就会消失。
Thanks.
谢谢。
回答by chris neilsen
You could try setting calculation to manual and turn off screen updating while the code runs
您可以尝试将计算设置为手动并在代码运行时关闭屏幕更新
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
your code here
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
回答by eh1160
I had the same problem, but turning off calculations, screen updating, etc. didn't work for me. After searching the web and tweaking my VBA for over an hour, here is my work-around:
我遇到了同样的问题，但关闭计算、屏幕更新等对我不起作用。在网上搜索并调整我的 VBA 一个多小时后，这是我的解决方法：
Range("NamedTable").Clear
Range("NamedTable").RemoveDuplicates Columns:=1
The first line clears all the rows from the table that is named "NamedTable", leaving the header row (if it exists).
第一行清除名为“NamedTable”的表中的所有行，保留标题行（如果存在）。
The second line takes advantage of the fact that all the cells in the table are now blank, and removes all the duplicate rows (analyzing the "duplicates" in the first column)
第二行利用表格中的所有单元格现在都是空白的事实，并删除所有重复的行（分析第一列中的“重复项”）
回答by user2675167
I know, it's a bit late but..
我知道，有点晚了但是..
listobject.DataBodyRange.Rows.Delete
A little help for future users ;o)
对未来用户的一点帮助;o)
