welllll..........I'm embarrassed to say, I can't seem to reporduce the
problem now either...........I'm using XL97SR2, (English version) but the
pop-up was coming up regularly in that other session. The range I'm using
is named "MyRange" and it covers B1:B7 whose values I vary from numbers to
text in my tests, and it all seems to work fine.

I tested =GlueText(MyRange) and =GlueText(B1:B7) and both work. I did add
one line of code specifying the delimiter, in between the two lines
below.....I don't know if that's the place to put it or not, but it seems to
work ok.........

'keepITcool
delimiter = Range("a1").Value
Dim rArea, rCell, r&, c&, s$

So, I guess the bottom line is, "I'm a Happy Camper", and I really do
appreciate your time, and the help you have given me here.........First
Class!

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"keepITcool" wrote:



I justed tested with xl97 SR1 (nl version),
but can't reproduce your runtime error.

press DEBUG when you get popup.
then look WHERE it produces the error.

pls also tell me what is your formula
AND the content of your Data range.



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


CLR wrote in

It's working fine, but I frequently get "Type Mismatch" error, which
will cancell out and continue to work fine............I'm using
XL97.....can I do anything to prevent the "Type mismatch" popups?

Oh yeah, thanks especially for anticipating me and including the
"delimiter" feature......that's really cool.........

Thanks,
Vaya con Dios,
Chuck, CABGx3



"keepITcool" wrote:

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