vba - 如何使连接函数更快

Question

大家好，希望你一切顺利。我有一个代码依赖于稍后使用的串联过程。我进行连接的代码如下:

i=2 
Do while ws.cells(i,2) <> 0
 ws.cells(i,1) = "=concatenate(C" & i & ", D" & i & ")" 
 i = i + 1 
Loop

问题是如果我有一个大样本，则需要太多时间才能完成。你们知道有什么方法可以让它变得更好更快吗？提前致谢

Answer 1

公式和数组之间的区别:

Formula - Total Rows: 1,048,576, Time: 2.414 sec
Arrays  - Total Rows: 1,048,576, Time: 3.758 sec

<小时/>

Option Explicit

Public Sub JoinCDinA1()
    Dim ws As Worksheet, lr As Long, tr As String, t As Double

    t = Timer
    Set ws = Sheet1
    lr = ws.UsedRange.Rows.Count

    With ws.Range("A2:A" & ws.UsedRange.Rows.Count)
        .Formula = "= C2 & D2"
        .Value2 = .Value2
    End With

    tr = "Formula - Total Rows: " & Format(lr, "#,###,###")
    Debug.Print tr & ", Time: " & Format(Timer - t, "0.000") & " sec"
End Sub

Public Sub JoinCDinA2()
    Dim ws As Worksheet, ur1 As Variant, ur2 As Variant, r As Long, lr As Long
    Dim tr As String, t As Double

    t = Timer
    Set ws = Sheet1
    lr = ws.UsedRange.Rows.Count
    ur1 = ws.Range(ws.Cells(2, 1), ws.Cells(lr, 1))
    ur2 = ws.Range(ws.Cells(2, 3), ws.Cells(lr, 4))
    For r = 1 To lr - 1
        ur1(r, 1) = ur2(r, 1) & ur2(r, 2)
    Next
    ws.Range(ws.Cells(2, 1), ws.Cells(lr, 1)) = ur1

    tr = "Arrays  - Total Rows: " & Format(lr, "#,###,###")
    Debug.Print tr & ", Time: " & Format(Timer - t, "0.000") & " sec"
End Sub