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.
@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
.Formula isn't faster if you want the methods to give the same end-point of cells populated with values (since you need the extra step of converting the cells with formulas to values, which makes it the slowest of the 3...)
Loop will be the fastest way. - Using, however a single-cell spill range target in an empty column (to prevent an overflow error) in vers. 2019+/MS365 including the value reconversion of the entire columns would speed up the Formula|Formula2 approach at least to a time comparable to the Evaluate approach.