vba - .End(xlDown) 与 .End(xlUp) 的性能影响

Question

假设第 1 到 5,000 列中有 25,000 到 50,000 行数据，每列可能有不同的行数。所有数据都是连续的，即列中没有空行，也没有空列。

考虑以下代码

Dim i As Long
Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets(1)
Dim LastColumn As Long
Dim LastRow As Long

With Ws1
    LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For i = 1 To LastColumn
        LastRow = .Cells(.Rows.Count, i).End(xlUp).Row
        ThisWorkbook.Worksheets(2).Cells(i,1).Value = "Column: " & i & "has " & LastRow & "rows."
    Next i
End With

在此代码中，我使用 .End(xlToLeft) 和 .End(xlUp) 找到了最后一列和最后一行的编号，它们从将工作表末尾返回到数据所在的位置。

我的问题是，从末尾回看是否会导致性能损失，如果您的数据是连续的，那么最好使用 .End(xlToRight) 和 .End(xlDown) 还是这种差异可以忽略不计？

Answer 1

我决定遵循 @Davesexcel 的极好建议，做一些时间实验:

Sub time_test(m As Long, n As Long, k As Long)
    Dim i As Long, r As Long, sum As Long, lastrow As Long
    Dim start As Double, elapsed As Double

    Application.ScreenUpdating = False

    lastrow = Range("A:A").Rows.Count
    Range(Cells(1, 1), Cells(lastrow, m)).ClearContents
    For i = 1 To m
        r = Application.WorksheetFunction.RandBetween(n, k)
        Cells(1, i).EntireColumn.ClearContents
        Range(Cells(1, i), Cells(r, i)).Value = 1
    Next i
    Debug.Print m & " columns initialized with " & n & " to " & k & " rows of data in each"
    Debug.Print "testing xlDown..."

    start = Timer
    For i = 1 To m
        sum = sum + Cells(1, i).End(xlDown).Value
    Next i
    elapsed = Timer - start
    Debug.Print sum & " columns processed in " & elapsed & " seconds"

    sum = 0

    Debug.Print "testing xlUp..."
    start = Timer
    For i = 1 To m
        sum = sum + Cells(lastrow, i).End(xlUp).Value
    Next i
    elapsed = Timer - start
    Debug.Print sum & " columns processed in " & elapsed & " seconds"
    Application.ScreenUpdating = True

End Sub

像这样使用:

Sub test()
    time_test 1000, 5000, 10000
End Sub

这会产生输出:

1000 columns initialized with 5000 to 10000 rows of data in each
testing xlDown...
1000 columns processed in 0.1796875 seconds
testing xlUp...
1000 columns processed in 0.0625 seconds

这表明使用 xlUp 更好。

另一方面，如果我运行 time_test 5000, 500, 1000

我得到输出:

5000 columns initialized with 500 to 1000 rows of data in each
testing xlDown...
5000 columns processed in 0.08984375 seconds
testing xlUp...
5000 columns processed in 0.84375 seconds

其中明显的优势被翻转了。我尝试了多种不同的参数选择，但无法获得清晰的信号。

如果我尝试运行 time_test 5000, 25000, 50000(这就是您所问的问题)，Excel 会崩溃，并显示一条错误消息，表明 Excel 缺乏资源来完成任务 - 在它到达任务之前时序阶段。

总而言之，任何差异似乎都很小，并且可能取决于实际使用的列数和行数。如果您所处的情况可能会产生影响，那么您可能正在针对 Excel 内存限制运行，在这种情况下，xlDown 和 xlUp 之间的差异可能是您的最小差异。担心。