Works perfectly Gord - and please excuse my noobness, but how do I make the
function available to all spreadsheets that I open without have to recreate
the function each time?

Ray D

"Gord Dibben" wrote:

Not without a User Defined Function like this one.

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 & " "
' for comma-delimited change above " " to ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =concatrange(A1:Z1)

No blank cells will be ignored.

For similar methods with code see this search result from google

http://tinyurl.com/6ao6k4


Gord Dibben MS Excel MVP

On Wed, 6 Aug 2008 06:46:01 -0700, anthony561fl
wrote:

This works great. However, what if Im wanting to combine several columns, say
50 or 100 columns worth of data? Id hate to have to enter each cell name in
that formula. Is there a way to specify a range of columns or cells rather
than each one before and after ampersands?