时间戳

首页 > 解决方案 > VBA 函数执行任务非常慢

excel - VBA 函数执行任务非常慢

问题描述

我在工作表中有大量数据，我一直在使用这些函数通过匹配条件来删除行，并且执行任务非常慢。

我希望得到一些帮助，使其更快。任何帮助将不胜感激。

如果这可以转换为 1 个代码，那将是很大的帮助。

       Sub MyList()
    
    Dim Listing     As Worksheet
    Dim LastRow     As Long
    
    LastRow = Function1.GetLastFilledRowNo(Listing)
    
    For RowNo = LastRow To 9 Step -1
        SKU = Format(Listing.Cells(RowNo, 4), "0000000")
        
        RowNoActive = Function2.GetRowNo_BySku(SKU)
        
        If RowNoActive > 0 Then
            Listing.Rows(RowNo).Delete
        End If
        
    Next RowNo
End Sub


Public Function GetLastFilledRowNo(Sht As Worksheet) As Long
    GetLastFilledRowNo = Sht.Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
End Function


Public Function GetRowNo_BySku(FormattedSku As String) As Long
    GetRowNo_BySku = Function3.GetRowNoSearchOneColumnByString( _
                     Sheet1, FormattedSku, 2)
End Function


Public Function GetRowNoSearchOneColumnByString(SheetName As String, StringToFind As String, ColumnName As String) As Long
    
    On Error GoTo GetRowNoSearchOneColumnByString_Error
    
    Dim StrFormula  As String
    GetRowNoSearchOneColumnByString = WorksheetFunction.Match(StringToFind, ThisWorkbook.Worksheets(SheetName).Range(ColumnName & ":" & ColumnName), 0)
    
    Exit Function
    
    GetRowNoSearchOneColumnByString_Error:
    GetRowNoSearchOneColumnByString = 0
End Function

标签： excelvba

解决方案

好吧，你应该关闭计算和屏幕更新。如果这还不够，请确保您收集所有要在变量中删除的行，Union()并在最后一次删除它们（这比单独删除每一行要快）。

Sub MyList()  
    Dim Listing     As Worksheet
    Dim LastRow     As Long
    
    LastRow = Function1.GetLastFilledRowNo(Listing)

    Dim RowsToDelete As Range  ' collect all rows

    For RowNo = LastRow To 9 Step -1
        SKU = Format(Listing.Cells(RowNo, 4), "0000000")
        
        RowNoActive = Function2.GetRowNo_BySku(SKU)
        
        If RowNoActive > 0 Then
            ' don't delete here just collect all rows to delete
            If RowsToDelete Is Nothing Then
                Set RowsToDelete = Listing.Rows(RowNo)
            Else
                Set RowsToDelete = Union(RowsToDelete, Listing.Rows(RowNo))
            End If
        End If
    Next RowNo

    ' if there is something collected then delete 
    If Not RowsToDelete Is Nothing Then
        RowsToDelete.Delete
    End If
End Sub

如果所有这些都没有显着加快速度，那么问题可能是您Match在每一行上运行该方法（这只会花费大量时间并且无法避免）。您可以测试是否将整个数据读入数组并在该数组上执行匹配使其更快。但为此，您需要更改代码的整个设计。

推荐阅读