Concatenate non-blank values in the same column and add commas

[chris.huang]

Hi,

I would like to concatenate all rows in the same column into one string, separated by comma and ignore all the blank cells. The code I have below will concatenate values across columns, but I actually want to concatenate values in the same column. Thanks in advance!

Chris

Desired:
A........B.......C
1........3........4
2........5........6
1,2.....3,4.....4,6

Code execution give me this:
A........B.......C
1........3........4 --> 1,3,4
2........5........6 --> 2,5,6


------------------------------------------------------------

Sub Concat()
  Dim UpperLeft As Range
  Dim Concat As String

  Set UpperLeft = Range("A1")

  For i = 0 To 9
    Concat = ""
    For j = 0 To 25
      UpperLeft.Offset(i, j).Select
      If UpperLeft.Offset(i, j).Value <> "" Then
        If Concat <> "" Then Concat = Concat & ","
        Concat = Concat & UpperLeft.Offset(i, j).Value
      End If
    Next j
    UpperLeft.Offset(i, 0).Value = Concat
  Next i
End Sub