# Combine 3 cells into 1 over 20000 lines in a faster way?

• ### Question

• Dear All,

I am having the problem, that i have an XLSM where i have several tabs (not more then 10). Each of these tabs I have part numbers splietted in 3 cells. for example aaa-bbb-ccc will look like A1=aaa B1=bbb and C1=ccc.

Tab contains average 2000 lines.

What I would like to do in D cell to create aaabbbccc, so for that i run =A1&B1&C1 code and after it, i will CTRL+C and paste as value.

I created a macro for this in the begining, but this macro now runs over 20 mins!!! (if i do it with hand with the above mentioned way, then it is only 5 mins :) )

My question is does somebody know a better way to do this? (faster?) My macro is pretty simple it does what i do with hand, but is really slow... Maybe i miss something. In my way of thinking this should not take more then 2 mins to create with macro on all tabs.

If somebody has a better solution for this than this snapshot from this macro, please share with me.

Many thanks!

```Sub Partcode_button()
Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
StartTime = Timer

Dim wsThis As Worksheet
Dim lRow As Long, NewRw As Long, i As Long, uccsosor As Long

Set wsThis = ThisWorkbook.Sheets("TAB1")
NewRw = 2
With wsThis
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lRow
.Range("AA" & NewRw).Value = .Range("A" & NewRw).Value & .Range("B" & NewRw).Value & .Range("C" & NewRw).Value
NewRw = NewRw + 1
Next i
End With

Set wsThis = ThisWorkbook.Sheets("TAB2")
NewRw = 2
With wsThis
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lRow
.Range("AI" & NewRw).Value = .Range("A" & NewRw).Value & .Range("B" & NewRw).Value & .Range("C" & NewRw).Value
NewRw = NewRw + 1
Next i
End With

Set wsThis = ThisWorkbook.Sheets("TAB3")
NewRw = 2
With wsThis
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lRow
.Range("AJ" & NewRw).Value = .Range("A" & NewRw).Value & .Range("B" & NewRw).Value & .Range("C" & NewRw).Value
NewRw = NewRw + 1
Next i
End With

'continue with this same on the rest of the tabs

'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

MsgBox "Data imported, everything went fine! Runtime: " & SecondsElapsed & " seconds."

End Sub```

Tuesday, September 15, 2015 9:26 PM

• Re:  fill a range

This ought to be faster...
'---

Set wsThis = ThisWorkbook.Sheets("TAB1")
NewRw = 2
With wsThis
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range(.Range("AA" & NewRw), .Range("AA" & lRow))
.Formula = "=A2&B2&C2"
.Value = .Value
End With
End With
'---

Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)
(free & commercial excel add-ins & workbooks)

Wednesday, September 16, 2015 1:24 AM
• Sorry my bad :(

.Formula = "=LEFT(H2,1)" works :) i have to use , instead of ;

Saturday, September 19, 2015 7:39 PM

### All replies

• Re:  fill a range

This ought to be faster...
'---

Set wsThis = ThisWorkbook.Sheets("TAB1")
NewRw = 2
With wsThis
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range(.Range("AA" & NewRw), .Range("AA" & lRow))
.Formula = "=A2&B2&C2"
.Value = .Value
End With
End With
'---

Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)
(free & commercial excel add-ins & workbooks)

Wednesday, September 16, 2015 1:24 AM
• Hi hlpbob,

This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi Zhang
TechNet Community Support

Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

Wednesday, September 16, 2015 6:26 AM
• Hello Jim,

Your script will do it in 0,03sec on 4000 lines, while the old one was about 10 mins :) MANY THANKS!

I try to improve and i changed the .Formula line to this:

.Formula = "=LEFT(H2;1)"

But with this i receive a 400 error (no error message), Do you know how can i implement the LEFT function as well? (i thought replaceing the =A2&B2... with the =LEFT will do it, but not :(

Many thanks for the help in advance!

Saturday, September 19, 2015 7:30 PM
• Hello Emi,

Thanks for that! Sorry that i posted in wrong topic.

Saturday, September 19, 2015 7:30 PM
• Sorry my bad :(

.Formula = "=LEFT(H2,1)" works :) i have to use , instead of ;

Saturday, September 19, 2015 7:39 PM
• Re:  formula error with VBA

Does...    =LEFT(H2;1)   ...return a value when entered manually on the worksheet?
In my part of the world (USA), a comma is required not a semicolon.

'---
Jim Cone
• Edited by Sunday, September 20, 2015 2:27 PM
Sunday, September 20, 2015 2:26 PM
• Hi Jim,

As far as I know, the list separator is different in the different country. We can also set it via Control Panel\Clock, Language, and Region->Formats->Additional settings->List separator.

Hope it is helpful.

Regards & Fei

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Monday, September 21, 2015 9:09 AM
• If you turn the rows/columns into a table (format as table) then add a formula to concatenate the data into D1, when you press <enter> the table will be expanded to add column D and the formula automagically copied down to all rows. It's pretty slick, takes no coding, and works like a charm.
Monday, September 21, 2015 2:05 PM