Concantate formatting issue

[JonRowland]

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

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.

 

[Fluff]

Try putting a space after the comma ", " for the join.

 

[Fluff]

Another option if you don't want the space is

Sub ConcatCells()
   With Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .NumberFormat = "@"
      .Value = Evaluate(.Offset(, -2).Address & "&"",""&" & .Offset(, -1).Address)
   End With
End Sub

 

[JonRowland]

Fluff

Should of said that I didn't want to add a space. However, your second option does what I've been trying to do.

Prefer and thanks for your help.
Jon

 

[Fluff]

You're welcome & thanks for the feedback.