VBA宏崩溃Excel
问题描述:
嗨伙伴们,VBA宏崩溃Excel
我正在运行一个宏来删除包含特定值的整个行。该代码在小数据集上工作正常,但在当前版本(约22,000条记录)上,它一直崩溃Excel(2010)。代码如下。在将数据拆分成更小的块并一次又一次地运行宏之后,我不知道该怎么做。
赞赏任何帮助和下面的代码:
Sub CleanOcc()
'Row counting
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim Lrow2 As Long
With Sheets("Occ_Prep")
'Cleans the occ_prep sheet ready for upload (Column and value can be changed)
Sheets("Occ_Prep").Activate
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow2 = Lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(Lrow2, "K")
If Not IsError(.Value) Then
If .Value = "0" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.
End If
End With
Next Lrow2
End With
End Sub
答
同意亚洲时报Siddharth评论自动筛选的路要走。这应该快很多。
Option Explicit
Sub delrows()
Dim ws As Worksheet
Dim LR As Long
Dim rng As Range, frng As Range
Application.ScreenUpdating = False
Set ws = Sheets("dataset") '<-- Change this to name of your worksheet
With ws
LR = .Range("A" & Rows.Count).End(xlUp).Row
.AutoFilterMode = False
Set rng = .Range("A1:C" & LR) '<-- Assuming K is the last column
rng.AutoFilter 3, "0" '<-- 11 referes to Column K
Set frng = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible) '<-- Don't delete the header
frng.EntireRow.Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
编辑:我刚在〜5秒钟内清理了20000行(3列)的数据。显然这取决于有多少比赛。
Excel和真正大量的数据不会混合,它不是真的意味着数据库:\ – 2013-05-01 21:21:12
这是找到最后一个单元格的一种非常错误的方法。请参见[THIS](http://*.com/questions/11169445/error-finding-last-used-cell-in-vba)另请参阅[THIS](http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/da7d1600-43bc-4e04-b2cc-8c4871349964)这里我使用'delrange'对象来删除相关的行。只需修改它以适应您的需求。 – 2013-05-01 21:41:00
另一种方式。使用自动过滤器在'0'上过滤Col K,然后删除相关的行。参见[THIS](http://*.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s )这显示了如何使用过滤的范围。只需修改它即可删除您的范围 – 2013-05-01 21:43:41