如何加速concatenate函数

Question

大家好，希望你们一切都好。我有一段代码，后面需要使用拼接过程。我进行拼接的代码如下：

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

问题是如果我有一个大样本，完成需要太多时间。你们知道任何使它更好、更快的方法吗？先谢谢了。

- user9585960

---

首先，永远不要使用 concatenate。 "AAA" & "BBB" = "AAABBB"。如果你正在连接一堆单元格并且拥有 Excel 2016，请坚持使用工作表函数 TEXTJOIN。 - ashleedawg

Answer 1

将一个数组批量加载、处理该数组，然后将结果一次性放回工作表，通常比使用循环快得多。

dim i as long, vals as variant
with worksheets("sheet1")
    vals = .range(.cells(2, "C"), .cells(.rows.count, "B").end(xlup).offset(0, 2))
    for i=lbound(vals, 1) to ubound(vals, 1)
        vals(i, 1) = join(array(vals(i, 1), vals(i, 2)), vbnullstring)
    next i
    redim preserve vals(lbound(vals, 1) to ubound(vals, 1), 1 to 1)
    .cells(2, "A").resize(ubound(vals, 1), 1) = vals
end with

- user4039065

---

谢谢@Jeeped！您的回答增加了我的知识，明天会尝试一下。 - user9585960

Answer 2

公式和数组的区别：

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

- paul bica

---

嘿，感谢你的帮助。我明天会尝试一下，但我相信数组就是我需要的。 - user9585960

很高兴能帮到你！ - paul bica

实际上，我提出了数组方法，以防字符串连接比原始问题示例更复杂，并且好处应该在更复杂的情况下展现出来。 - user4039065

Answer 3

有几种方法可以一次性分配而无需循环。例如：

ws.Range("A2:A" & ws.UsedRange.Rows.Count - 1).Formula = "= C2 & D2"

- Slai