☰

删除可见行需要很长时间才能处理

tag-icon • tag-icon microsoft-excel vba
删除可见行需要很长时间才能处理

我有以下函数，它提供了一个选项来选择范围，然后删除所有可见行。如果我处理小型数据集，这个函数就很好用。

但是现在我正在处理包含许多列和 400 到 500K 行的大型数据集。我使用以下函数过滤并删除了一些转储数据。

有什么方法可以使其更高效，更快速。

我确实希望能有更好的方法来实现这一点。非常感谢您的帮助。

Dim myRange As Range
Set myRange = Application.Selection

Set myRange = Application.InputBox("Select one Range that you want to delete visible rows", "DeleteAllVisibleRows", myRange.Address, Type:=8)

myRange.SpecialCells(xlCellTypeVisible).Delete

答案1

也许我明白了你的程序运行如此缓慢的原因。

如果 @VBasic2008 是正确的，并且您确实使用过滤器有条件地选择要删除的某些行，则该.SpecialCells(xlCellTypeVisible) 方法将返回由许多不同区域组成的范围。为了清除这样的范围，Excel 实际上会清除每个区域，这需要很长时间。

您可以作弊并预处理要删除的数据，将它们组合成一个连续的范围。在这种情况下，Excel 的处理速度会快得多：

Sub SortRngAndDelVisibleRows()
Dim ws As Worksheet
Dim sourceRange As Range, indexRange As Range, delRange As Range, oCell As Range
Dim iCountToRest As Long, iCountToDel As Long
Dim oldDisplayAlerts As Boolean
    Set ws = ActiveSheet
    If ws.AutoFilter Is Nothing Then Exit Sub
    
    oldDisplayAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set sourceRange = ws.AutoFilter.Range.Resize(ws.AutoFilter.Range.Rows.Count - 1).Offset(1)
Rem Thanks @VBasic2008 for a simple way to select the entire autofilter body.
Rem Let's create an auxiliary column next to the processed range
Rem and fill its invisible cells with row numbers.
    Set indexRange = sourceRange.Offset(0, sourceRange.Columns.Count).Resize(, 1)
    indexRange.Clear
    For Each oCell In indexRange.Cells
Rem Visible cells are skipped and left blank (we'll count it later)
        If oCell.EntireRow.Hidden Then oCell.Value = oCell.Row
    Next oCell
Rem (It would be possible to set any value, but the row numbers will allow
Rem you to keep the order of the records during subsequent sorting)    
    iCountToDel = WorksheetFunction.CountBlank(indexRange)
    iCountToRest = sourceRange.Rows.Count - iCountToDel
    
Rem Disable the filter and sort the range in ascending order of values in the auxiliary column.
Rem Now all records without a number will be at the end of the set.
    If ws.FilterMode = True Then ws.ShowAllData
    sourceRange.Resize(, sourceRange.Columns.Count + 1).Sort _
        key1:=indexRange.Cells(1), order1:=xlAscending, Header:=xlGuess
Rem Now it can be deleted
Rem Since the rows to be deleted are now in the same contiguous range,
Rem the deletion process is much faster.
    Set delRange = sourceRange.Resize(iCountToDel).Offset(iCountToRest, 0)
    delRange.Rows.Delete
Rem Or it can be just cleared - the result should be the same
'    delRange.Clear
Rem The auxiliary range is also no longer needed
    indexRange.Clear
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = oldDisplayAlerts
End Sub

请尝试一下

答案2

成本来自于实时刷新 Excel 表。

更改要包含的代码Application.ScreenUpdating = False以防止工作表在运行时动态更新。

相关内容