CONCATENATE a range of cells

[jim oswell]

Range name: ctc1 a1:a1000
a1=2
a2=sam
a3=0
a4=9
a5=""
a6=7
a7:a1000=""
=CONCATENATE(a1:a1000) where cl.value<>""

answer should be 2,sam,0,9,7
How do you code this condition?

[David Hilberg]

The concatenate function doesn't accept a range of cells.
You can create a user-defined function with code like this:

'Accepts a single range only.
Function ConcatRange(Cellblock As Range) As String
Application.Volatile True
For Each Cell In Cellblock
ConcatRange = ConcatRange & Cell.Value
Next
End Function

EXAMPLE OF USE:
=Personal.xls!ConcatRange(A1:B3)

INSTEAD OF
=Concatenate(A1,A2,A3,B1,B2,B3)

You can modify the code to include a comma as delimiter if that's what
you want.

- David Hilberg

On Wed, 29 Aug 2001 09:47:50 -0700, "jim oswell" <jos...@heery.com>
wrote:

_____________
Please reply to newsgroups for follow-up; For personal messages,
remove "nöspäm" from address.

[Don Guillett]

Look in help for find to get you going

--
Don Guillett
SalesAid Software
Granite Shoals, TX
don...@281.com
jim oswell <jos...@heery.com> wrote in message
news:1913801c130aa$5725e780$9be62ecf@tkmsftngxa03...

[Harlan Grove]

David Hilberg <davidh@nöspäm.ericmarder.com> wrote...

>The concatenate function doesn't accept a range of cells.
>You can create a user-defined function with code like this:
>
>'Accepts a single range only.
>Function ConcatRange(Cellblock As Range) As String
> Application.Volatile True
> For Each Cell In Cellblock
> ConcatRange = ConcatRange & Cell.Value
> Next
>End Function

A bit more generic would be better.

'---- begin VBA ----
Function mcat(ParamArray s()) As String
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 & y
Next y
Else
mcat = mcat & x
End If
Next x
End Function
'---- end VBA ----

Then insert commas between nonblank fields using the array formula

=SUBSTITUTE(mcat(IF(ctc<>"",ctc&",","")),",","",COUNTA(ctc))