Building a string

[geofferrington]

Hi there.

I have a column of values, which is interspersed with empty cells, that
typically looks like the following:

red
green

blue
yellow


black

purple
orange


How can I build a string that looks like the following?

red,green,blue,yellow,black,purple,orange

Any ideas? - greatly appreciated.

Geoff

 

[Tom Ogilvy]

with code?

Sub BringTogether()
Dim rng as Range, cell as Range
Dim s as String
set rng = Columns(1).SpecialCells(xlConstants,xlTextValues)
for each cell in rng
s = s & trim(cell.Value) & ","
Next
s = Left(s,len(s)-1)
msgbox s
End Sub

 

[geofferrington]

Tom

Thankyou for your response to my problem. From my past experiences with
you I know that the solution that you offer is very eloquent. However,
I think the solution to my problem lays with a function. To this end I
have taken your code and tried to modify it. Unfortunately that is not
working too well either.

The following is my attempt so far: What I am trying to do, is run the
function with a named range (("colJ") as a parameter.

Function BringTogether(rng) As Range
Dim rng As Range, cell As Range
Dim s As String
'----Set rng = Columns(J).SpecialCells(xlConstants, xlTextValues)
Set rng = rng.SpecialCells(xlConstants, xlTextValues)
For Each cell In rng
s = s & Trim(cell.Value) & ","
Next
BringTogether = Left(s, Len(s) - 1)
End Function

Regards
Geoff

 

[geofferrington]

Thnkyou Tom.

Your response did, of course, have the essentials of what I was after.
and using that I have come up with a UDF which will suit my purposes
just fine.
The following is what I have decided to run with:

Function BringTogether(rng As Range) As String
Dim cell As Range
Dim s As String
For Each cell In rng
If cell > "" Then s = s & Trim(cell.Value) & ","
Next
BringTogether = Left(s, Len(s) - 1)
BringTogether = "(" & BringTogether & ")"
End Function

Regards
Geoff