VBA Concatenate 2 columns without loop-CodePudding

I have my column B with Strings like "6L2AAB". My column D with Strings like "E3" I'd like to produce in my column J the concatenation of B&D, for instance "6L2AABE3", for each row

My code throws a "13" error

.Range("J:J").Value = .Range("B:B").Value & "" & .Range("D:D").Value

Is there a way to do this without a loop ? Thanks

CodePudding user response：

Edit: added loop-based approach to compare timing. Loop is faster!

Your code doesn't work because (eg) .Range("B:B").Value returns a 2-dimensional array, and you can't concatenate the contents of two arrays using &

You can use the worksheet's Evaluate method:

Sub tester()
    
    Dim t, i As Long, arr1, arr2, arr3, x As Long
    
    t = Timer
    With ActiveSheet
        .Range("J:J").Value = .Evaluate("=B:B&D:D")
    End With
    Debug.Print "Evaluate", Timer - t
    
    
    t = Timer
    With ActiveSheet
        arr1 = .Range("B:B").Value 'read input values
        arr2 = .Range("D:D").Value
        ReDim arr3(1 To UBound(arr1), 1 To 1) 'size array for output
        
        For i = 1 To UBound(arr1, 1)          'loop and concatenate
            arr3(i, 1) = arr1(i, 1) & arr2(i, 1)
        Next i
        .Range("J:J").Value = arr3 'populate output to sheet
    End With
    Debug.Print "Loop", Timer - t
    
End Sub

Maybe don't run it on the whole column unless you really need that though.

CodePudding user response：

@Tim Williams It really depends on the number of rows, if you are working with larger amounts of data loops start to slow slightly and evaluate will jump ahead in processing speed just slightly. But there is another option that is faster than both, the .formula method. See Tim's code with it added in below using > 1 million rows with .formula added.

Average for me:

Formula 1.664063

Evaluate 3.675781

Loop 3.824219

Sub tester()
    
    Dim t, i As Long, arr1, arr2, arr3, x As Long
    
    t = Timer
    With ActiveSheet
        .Range("C:C").Formula = "=A:A&B:B"
    End With
    Debug.Print "Formula", Timer - t
    
    t = Timer
    With ActiveSheet
        .Range("C:C").Value = .Evaluate("=A:A&B:B")
    End With
    Debug.Print "Evaluate", Timer - t
    
    
    t = Timer
    With ActiveSheet
        arr1 = .Range("A:A").Value 'read input values
        arr2 = .Range("B:B").Value
        ReDim arr3(1 To UBound(arr1), 1 To 1) 'size array for output
        
        For i = 1 To UBound(arr1, 1)          'loop and concatenate
            arr3(i, 1) = arr1(i, 1) & arr2(i, 1)
        Next i
        .Range("C:C").Value = arr3 'populate output to sheet
    End With
    Debug.Print "Loop", Timer - t
    
End Sub