Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Set x = Range(ActiveCell, ActiveCell.End(xlDown))
For Each y In x
If Len(y.Text) 0 Then sbuf = sbuf & y.Text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben Excel MVP

On Tue, 15 Nov 2005 09:02:05 -0800, "washdcjohn"
wrote:

I'm trying to write a macro that will loop through a column and copy the data
to a single concatenated string which will be pasted to another cell. The
macro should step through the column of data until it reaches the end of the
data set or an empty cell. I could name a range if needed. Any help
getting me started???? The concatenate( ) function won't work with
ranges..., so I assume I'll have to write a macro to loop.