Copy this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

It will ignore blank cells.

Usage is =concatrange(range)


Gord Dibben MS Excel MVP

On Mon, 28 Sep 2009 09:52:02 -0700, Jorge E. Jaramillo
wrote:

Thank you guys, I used the "&" and it worked.

But now I want to make it even more sophisticated. It is not uncommon that
some of the values are empty, so in the summary cell it is shown: AB1,
XO2,,VA5,,,,LN2 (being AB1, XO2,,VA5,,,,LN2 the contents of the original
cells and not having anything to do with the names of those cells).

Would it be possible to add a condition that if the origin cell is empty,
not to add the comma?

Jorge E Jaramillo
"Jorge E. Jaramillo" wrote:

I need to consolidate the results of all the sheets of a workbook in a final
sheet. For this I use the concatenate function. To try to organize the
results, I added a comma in between each value (",",) but since the formula
requires the name of each tab and cell and some of the names of the tabs are
kind of long, I am getting an error message about entering too many
parameters. My workaround was to remove some of the commas at the end of the
formula, and the formula works now this way, but now the results look
difficult to understand.

Could someone please tell me what the limit of parameters is and if is there
a way to solve this?


Jorge E Jaramillo