excelvba

VBA Concatenate 2 columns without loop


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


Solution

  • 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.