quicker cell concatenation

[Valeria]

Dear experts,
I want to fill in a range (column) with concatenation data from other 4
columns.
The number of rows is quite big so it takes a while before vba can finish
the task. My code looks like
For i = 1 to LastRow
Cells(i, 7)=Cells(i,1) & Cells(7,2) & Cells(i,3) & Cells(1,4) & Cells(i,5) &
Cells(i,6)
Next i

Is there another way to do this more quickly, maybe playing with ranges?
Thanks a lot for your advice.
Kind regards

 

[Per Jessen]

Hi

Turn off screenupdating and set calculation to manual, just remember
to set it back again:

Sub aaa()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

For i = 1 to LastRow
Cells(i, 7)=Cells(i,1) & Cells(7,2) & Cells(i,3) & Cells(1,4) &
Cells(i,5) &
Cells(i,6)
Next i

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Regards,
Per

 

[JLatham]

Try replacing the loop with something like this:

Const newFormula = "=RC[-6] & R7C2 & RC[-4] & R1C4 & RC[-2] & RC[-1]"
Dim changeRange As Range

Set changeRange = Range(Cells(1, 7), Cells(lastRow, 7))
Application.ScreenUpdating = False
changeRange.FormulaR1C1 = newFormula
'if you need "hard values"
changeRange.Formula = changeRange.Value

 

[Jacob Skaria]

Try the below non-looping code

Range(Cells(1, 7), Cells(lastRow, 7)).Formula = "=A1&B1&C1&D1&E1&F1"
Range(Cells(1, 7), Cells(lastRow, 7)) = Range(Cells(1, 7), Cells(lastRow,
7)).Value

 

[Jacob Skaria]

The second line split to two....

Range(Cells(1, 7), Cells(lastRow, 7)).Formula = "=A1&B1&C1&D1&E1&F1"
Range(Cells(1, 7), Cells(lastRow, 7)) = _
Range(Cells(1, 7), Cells(lastRow, 7)).Value

 

[Valeria]

I am baffled - both solutions (Jabob and JLatham) work beautifully and make
me save at least 20 min/run! Thanks a lot!

 

[Rick Rothstein]

Valeria,

If you are still following this thread, the following macro should do what
you want and appears to be exceedingly fast (it did a sample of 10,000 rows,
literally, in the blink of an eye)...

Sub ConcatAthruF()
Dim X As Long, LastRow As Long, Vin As Variant, Vout As Variant
LastRow = Range("A:F").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Vin = Range("A1:F" & LastRow)
ReDim Vout(1 To LastRow)
For X = 1 To LastRow
Vout(X) = Vin(X, 1) & Vin(X, 2) & Vin(X, 3) & _
Vin(X, 4) & Vin(X, 5) & Vin(X, 6)
Next
Range("G1").Resize(LastRow) = Vout
End Sub

 

[JLatham]

The two are pretty much the same, except that mine uses R1C1 format for the
formula, and Jacob's uses the more familar addressing. Also, in mine, I did
reference $B$7 ( R7C2 ) and $D$1 ( R1C4 ) absolutely as per your code
example. I wasn't certain that's what you wanted to do, so I just emulated
your loop code literally.

The amazing part of it all is that Excel does all of the work for you, and
does it fast. Your own loop would probably increase speed dramatically if
you simply added
Application.ScreenUpdating=False
just before the loop starts. That alone can reduce time-to-complet for such
things by huge amounts. Making changes via an object reference as was done
by both myself and Jacob, is equally stunning in its results in most cases.
But after you've turned off ScreenUpdating, it becomes hard to measure the
added improvement of simply referencing objects rather than 'directly'
addressing them on a worksheet or in a workbook.

 

[Rick Rothstein]

Sorry, I posted the wrong version of the code (one of my old test lines was
left in it instead of the correct one). This code still executes 10,000 rows
of concatenations in the blink of an eye...

Sub ConcatAthruF()
Dim X As Long, LastRow As Long, Vin As Variant, Vout As Variant
LastRow = Range("A:F").Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Vin = Range("A1:F" & LastRow)
ReDim Vout(1 To LastRow)
For X = 1 To LastRow
Vout(X) = Vin(X, 1) & Vin(X, 2) & Vin(X, 3) & _
Vin(X, 4) & Vin(X, 5) & Vin(X, 6)
Next
Range("G1").Resize(LastRow) = WorksheetFunction.Transpose(Vout)
End Sub