将范围复制到 Variant 数组非常快.您的子例程在下面进行了修改和评论:

Copying the ranges to Variant arrays is quite fast. Your subroutine amended and commented below:

Sub CopyColumn(targetSheet As Worksheet, sourseSheet As Worksheet, LastRow As Long)

    ' LastRow as Integer will give an error for rows > 32,767, use Long instead
    ' Check the syntax: sourseCell, targetCell as Range means:
    ' sourceCell as Variant, targetCell as Range. We should include
    ' "as Range" after each variable declaration if we want it to be a Range

    Dim sourseCell As Range, targetCell As Range
    Dim lCount As Long
    Dim vTarget, vSource

    ' I kept the names targetCell, sourseSheet, but turned them to ranges
    ' You might want to change sourseSheet to sourceSheet

    With targetSheet
        Set targetCell = .Range(.Cells(14, "D"), .Cells(LastRow, "D"))
    End With

    ' I assume you mean sourceSheet instead of sourceCell, 
    ' in your original code?
    With sourseSheet
        Set sourseCell = .Range(.Cells(14, "CH"), .Cells(LastRow, "CH"))
    End With

    vTarget = targetCell.Value2
    vSource = sourseCell.Value2

    ' If there is a change you do not have numeric values 
    ' this needs error trapping
    For lCount = LBound(vTarget, 1) To UBound(vTarget, 1)
        vTarget(lCount, 1) = vTarget(lCount, 1) + vSource(lCount, 1)
    Next lCount

    targetCell.Value = vTarget

End Sub

测试:

Option Explicit
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub test_copy_column()
    Dim targetSheet As Worksheet, sourseSheet As Worksheet, LastRow As Long, _ 
    tick As Long
    ' Maybe change sourseSheet to sourceSheet :)

    tick = GetTickCount      ' Clock count

    Set targetSheet = Sheet1
    Set sourseSheet = Sheet1
    LastRow = 50000          ' I inputted random numbers for testing

    CopyColumn targetSheet, sourseSheet, LastRow

    MsgBox "Time to copy: " & GetTickCount - tick & " milliseconds"
End Sub

结果:

此处的相关问题

希望对您有帮助！