change (x) rows of data into a comma-delimited list

[Gingertrees]

I have a section of my sheet that allows the user to put in household member names, one member to a row. (In the other columns in each row they enter the members' demographic info.)

How can I turn a VARIABLE number of rows of names into a list in a different part of the sheet? So for example:
row 1: John
row 2: Billy
row 3: Sue
would appear elsewhere as: John, Billy, Sue

or
1: Amy
2: Kevin
3: Joe
4: Tom
5: Teri
would appear Amy, Kevin, Joe, Tom, Teri

I can think how to do it if I knew for certain the number of rows, =A1&", "&B1&... but I don't know how to do that and not end up with a lot of unused commas if I only have a small household. Help?

 

[jimrward]

i created this user defined function last week for something similar

Function myjoin(r As Range, d As String) As String

For Each cell In r
myj = myj & cell.Value & d
Next

myjoin = Left(myj, Len(myj) - 1)

End Function

once stored in the VBA editor, select the target cell where you want to have the finished data type = and then choose user defined function from the list, then select myjoin, the first requirement is the range of the source data, second is the delimiter

=myjoin(A1:A6,"~") will turn
Amy
Kevin
Joe
Tom
Teri

into amy~kevin~joe~tom~teri

 

[Gingertrees]

Cool! That works for a set number...but I want it to discern the used range. So, if my total range is A1:A8, but the household is only one or two people, I do not want this to appear:
Amy, , , , , , , ,
Amy, Kevin, , , , , , ,
I'd want:
Amy
Amy, Kevin


Alternately, if the user for reasons unknown starts typing in cell A3 omitting cells A1 and A2, This:
, , Amy, Kevin, , , , ,
vs. This:
Amy, Kevin

It seems like this has something to do with UsedRange, but I'm especially bad at that particular function...

 

[jimrward]

i have added an IF to the macro code

Function myjoin2(r As Range, d As String) As String

For Each cell In r
If Len(cell.Value) > 0 Then
myj = myj & cell.Value & d
End If
Next

myjoin2 = Left(myj, Len(myj) - 1)

End Function

 

[Gingertrees]

Great! That is going to be über-useful! Thanks a lot!
~G