JonRowland
Active Member
- Joined
- May 9, 2003
- Messages
- 417
- Office Version
- 365
- Platform
- Windows
Hi,
I am looking to bascially concantate cells from Cols C&D, separtgin the values with a comma. All seems easy but when the result is returned there values appear not has I would like.
For example
C: 123456
D: 654321
I would like combinated as 123456,654321 but end up with 123,456,654,321 and if look in cells the data is stored as 123456654321.
I've tried lots of different ways but code I with at the moment is
I could use Cells(2, 3).FormulaR1C1 = "=CONCATENATE(RC[1],"","",RC[2])" which works but then need to Copy & Paste as values which needs calucation turned back on and then off which adds a bit of time to the whole procedure I'm running.
Any help would be much appreciated.
I am looking to bascially concantate cells from Cols C&D, separtgin the values with a comma. All seems easy but when the result is returned there values appear not has I would like.
For example
C: 123456
D: 654321
I would like combinated as 123456,654321 but end up with 123,456,654,321 and if look in cells the data is stored as 123456654321.
I've tried lots of different ways but code I with at the moment is
VBA Code:
Sub ConcatCells()
Dim Row As Long
Dim LastRow As Long
Dim RC As Long
LastRow = Application.WorksheetFunction.CountA(Range("A:A"))
For Row = 2 To LastRow
Cells(Row, 5) = Join(Application.Transpose(Application.Transpose(Range(Cells(Row, 3), Cells(Row, 4)))), ",")
Next Row
End Sub
I could use Cells(2, 3).FormulaR1C1 = "=CONCATENATE(RC[1],"","",RC[2])" which works but then need to Copy & Paste as values which needs calucation turned back on and then off which adds a bit of time to the whole procedure I'm running.
Any help would be much appreciated.