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