=CONCATENATE(A1:A10) entered as array still only returns
the first element, A1.
There has to be something better than -
=CONCATENATE(A1,A2,A3,A4,A5,...A10)
Thanks
Function Concatall(rng As Range) As String
Dim cell As Range
For Each cell In rng
Concatall = Concatall & cell.Text
Next
End Function
---
Call as =Concatall(A1:A10)
HTH
Jason
Atlanta, GA
>.
>
That works. Thank you.
Or generalize,
Function mcat(ParamArray s()) As String
'Copyright (C) 2002, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
'------------------------------------
'string concatenation analog to SUM
Dim r As Range, x As Variant, y As Variant
For Each x In s
If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y
Else
mcat = mcat & x
End If
Next x
End Function
which allows expressions like
=mcat("hi",(A1:A4,A6:D6,C2),"foo",A7:F9)