try following udf


Public Function GlueText( _
data As Variant, _
Optional delimiter As String = vbNullString) As String
'keepITcool

Dim rArea, rCell, r&, c&, s$
If TypeOf data Is Range Then
For Each rArea In data.Areas
For Each rCell In rArea.Cells
'Note: for ranges the (formatted) Text property is used
If Len(rCell) Then s = s & delimiter & rCell.Text
Next
Next
ElseIf IsArray(data) Then
On Error Resume Next
c = LBound(data, 2) + 1
On Error GoTo 0
If c 0 Then GoTo TwoDim Else GoTo OneDim

TwoDim:
For r = LBound(data, 1) To UBound(data, 1)
For c = LBound(data, 2) To UBound(data, 2)
If Len(data(r, c)) Then s = s & delimiter & data(r, c)
Next
Next
GoTo theEND
OneDim:
For r = LBound(data) To UBound(data)
If Len(data(r)) Then s = s & delimiter & data(r)
Next
Else
s = data
End If
theEND:
GlueText = Mid(s, 1 + Len(delimiter))
End Function




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CLR wrote in

Hi All...........
With a UDF or a macro, would it be possible to CONCATENATE all the
cells in a selected Range, say A1:a7 without having to type each cell
address.........something like =SpecialCONCATENATE(A1:A7), or
=SpecialCONCATENATE(MyRange), of course those don't work, but that's
the idea.

TIA
Vaya con Dios,
Chuck, CABGx3