concatenate without looping - VBA

[ericlch16]

Hello

I have two column(A,B). 3rd column(C) is to concatenate column 1 &"-" & column 2 e.g

aaa bbb aaa-bbb
cc ww cc-ww

I remember there is a quick way to do column C without looping. The rows are in the thousands and I am trying to avoid looping.

.range(.cells(1,3), cells(lastrow, 3)) = ???

Thanks,

 

[Joe4]

I think you are looking for something like this:

    Dim lastrow As Long
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Range(Cells(1, 3), Cells(lastrow, 3)).FormulaR1C1 = "=RC[-2] & ""-"" & RC[-1]"

 

[EFANYoutube]

Hi eric
This is what I would do

    ThisWorkbook.Sheets("Sheet1").Range("C1").FormulaR1C1 = "=RC[-2]&RC[-1]"
    ThisWorkbook.Sheets("Sheet1").Range("C1").AutoFill Destination:=Range("C1:C20000"), Type:=xlFillDefault

 

[Joe4]

Hi eric
This is what I would do

    ThisWorkbook.Sheets("Sheet1").Range("C1").FormulaR1C1 = "=RC[-2]&RC[-1]"
    ThisWorkbook.Sheets("Sheet1").Range("C1").AutoFill Destination:=Range("C1:C20000"), Type:=xlFillDefault

Two issues with that approach:
1. You forgot the dash "-"
2. It is going to populate all the way down to row 20000, regardless on how many rows of data there are.
That is going to unnecessarily greatly increase the size of your file for no reason.
You typically do not want to do that, and instead dynamically find the last row of data and only populate that many rows.
(Also note that you can apply the formula to a multi-cell range all at once without having to use auto-fill, like I demonstrated).

 

[EFANYoutube]

Very intelligent sir

 

[trunten]

Looping is not as slow as you think. In fact it's quicker than a formula based apporach in my tests

Sub concatit()
    Dim t, a, b, i&, lastRow&
    t = Timer
    lastRow = Range("A:B").Find("*", Range("A1"), xlValues, 2, 1, 2, False).Row
    With Range("C1", Cells(lastRow, "C"))
        t = Timer
        .FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
        .Value = .Value
        Debug.Print "Not looping: " & Round(Timer - t, 2) & " seconds"
        t = Timer
        a = .Offset(, -2).Resize(, 2).Value
        ReDim b(1 To UBound(a), 1 To 1)
        For i = 1 To UBound(a): b(i, 1) = a(i, 1) & "-" & a(i, 2): Next i
        .Value = b
        Debug.Print "Looping: " & Round(Timer - t, 2) & " seconds"
        Erase a
        Erase b
    End With
End Sub

 

[ericlch16]

Looping is not as slow as you think. In fact it's quicker than a formula based apporach in my tests

Sub concatit()
    Dim t, a, b, i&, lastRow&
    t = Timer
    lastRow = Range("A:B").Find("*", Range("A1"), xlValues, 2, 1, 2, False).Row
    With Range("C1", Cells(lastRow, "C"))
        t = Timer
        .FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
        .Value = .Value
        Debug.Print "Not looping: " & Round(Timer - t, 2) & " seconds"
        t = Timer
        a = .Offset(, -2).Resize(, 2).Value
        ReDim b(1 To UBound(a), 1 To 1)
        For i = 1 To UBound(a): b(i, 1) = a(i, 1) & "-" & a(i, 2): Next i
        .Value = b
        Debug.Print "Looping: " & Round(Timer - t, 2) & " seconds"
        Erase a
        Erase b
    End With
End Sub

thank you. what is the optimal threshold for the formula to be faster than loop ? less than 1000 , 10000, 100000, 1Million rows ?

 

[Joe4]

Looping is not as slow as you think. In fact it's quicker than a formula based apporach in my tests

Sub concatit()
    Dim t, a, b, i&, lastRow&
    t = Timer
    lastRow = Range("A:B").Find("*", Range("A1"), xlValues, 2, 1, 2, False).Row
    With Range("C1", Cells(lastRow, "C"))
        t = Timer
        .FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
        .Value = .Value
        Debug.Print "Not looping: " & Round(Timer - t, 2) & " seconds"
        t = Timer
        a = .Offset(, -2).Resize(, 2).Value
        ReDim b(1 To UBound(a), 1 To 1)
        For i = 1 To UBound(a): b(i, 1) = a(i, 1) & "-" & a(i, 2): Next i
        .Value = b
        Debug.Print "Looping: " & Round(Timer - t, 2) & " seconds"
        Erase a
        Erase b
    End With
End Sub

No, loops are definitely are slower than formula approach (not to mention that the code is also longer and a lot more complex).

I ran two tests on my code and your code, each on 10000 records.
Your code took 0.10 and 0.09 seconds to run.
Mine took 0.03 and 0.02 seconds to run.

Don't make things needlessly complicated when they do not need to be!

 

[EFANYoutube]

All rows 1,000,000 took:
Not looping: 4.78
Looping: 4.32

 

[ericlch16]

No, loops are definitely are slower than formula approach (not to mention that the code is also longer and a lot more complex).

I ran two tests on my code and your code, each on 10000 records.
Your code took 0.10 and 0.09 seconds to run.
Mine took 0.03 and 0.02 seconds to run.

Don't make things needlessly complicated when they do not need to be!

Thanks. that's what i originally thought. From previous recommendation on this forum, i changed the way i code. I tried to avoid looping as much as possible because i have to deal with so many rows of data.

Thanks for your expertise once more. (your username is very familiar to me and i think I learned a lot from your previous posts)