Hi,

I always use this, works horizontal and vertical range.

Jack Sons.


'************************************************* *************************
'Purpose: Merge cells, retaining all data
'Inputs: Selection
'Returns: Merged values in the first cell of rRng
'************************************************* *************************

' Merging the Selection INTO ONE CELL
'This macro merges the entire Selection into one cell. To include a
delimiter,
'change SDELIM, or insert columns between the cells and put the delimiter
'in those columns (make sure the last column is a delimiter column as well.

Public Sub MergeToOneCell()
'J.E. McGimpsey, http://www.mcgimpsey.com/excel/mergedata.html
Dim sDELIM As String 'Const sDELIM As String = ", "
sDELIM = InputBox("Input one or more characters for delimiter")
Dim rCell As Range
Dim sMergeStr As String
With Selection
For Each rCell In .Cells
sMergeStr = sMergeStr & sDELIM & rCell.text
Next rCell
Application.DisplayAlerts = False
.Merge Across:=False
Application.DisplayAlerts = True
.Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))
End With
Selection.UnMerge
ActiveCell.Select
End Sub
"kraway" schreef in bericht
...
I want to take a column of e-mail addresses and convert them into a list
seperated by a comma. Is there a way to do it? I have Excel 2000.

--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
116 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!