Hi-light the range and run Builder, then select an empty cell and run Putter. Here are the macros:
Dim sf As String
Sub builder()
sf = ""
For Each r In Selection
If sf = "" Then
sf = "=" & r.Address
Else
sf = sf & "&" & r.Address
End If
Next
sf = Replace(sf, "$", "")
MsgBox sf
End Sub
Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function
usage is: =ConCatRange(A1:A10)
This gives a comma/space delimited list in one cell.
You can adjust that to suit by changing or removing & ", "
With xl2000 or later versions, you can use the VBA.Join function.
--
Jim Cone
Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware
(Data Options add-in: rows/dates/random stuff)