Sorry I not read it good
Roger posted a suggestion for you in the Excel user group
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Ron de Bruin" wrote in message ...
There are no errors when you use the function from J.E when there are empty cells in the range
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Excel Helps" wrote in message ...
Thanks Ron
But I can't fathom out what I need to remove the #VALUE! error from your link.
I'm hoping that Roger may pick up this thread as he forwarded me the piece of code which works fine for my needs apart from the
#VALUE! error.
My limited experience with VB code is not sufficient to alter the code.
"Ron de Bruin" wrote:
I posted this in the Excel user group
See J.E's function
http://www.mcgimpsey.com/excel/udfs/multicat.html
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Excel Helps" wrote in message ...
How do I remove the #VALUE! from my output file when running the function below.
Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error.
Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) 0 Then
sbuf = sbuf & Cell.Text & Chr(166)
sbuf = sbuf & Cell.Text & "|"
End If
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function