Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(a1:a10,"|") desired delimiter between quotes
'ignores blanks and zero cells
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text < "" And Cell.text < 0 Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange = Left(sbuf, Len(sbuf) - Len(Delim))

End Function


Gord Dibben MS Excel MVP

On Thu, 21 May 2009 18:20:14 -0700, art
wrote:

Thank you. However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.

"Jacob Skaria" wrote:

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Hello:

Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

Is there a way to do it with an array or range formula?

Thanks for any help.

Art.