selecting a range

[Garth]

I just learned about the CONCATENATE function and it seems like what I need
but I often have a good number of cells to pick my data from. Is there a way
to select a range of cells, like A1:A5, in lieu of selecting each individual
cell A1,A2,A3,A4,A5 ?

 

[Sheeloo]

I don't think you can do that...

I use an helper column
Enter
=A1 in B1
Enter
=B1 & ", ", A2 in B2
and copy down...

Last cell gives me the concatenation of all cells in Col A, upto that row.

 

[Gord Dibben]

Garth

You could use this UDF to select a range.

Results would be comma de-limited. Adjust that to suit.

Function ConCatRange(CellBlock As Range) As String
'for a single range =ConCatRange(A1:A10)
'for non-contiguous ranges =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 & ","

'adjust "," to suit like " " for space de-limited

Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Also ignores blank cells in the range.


Gord Dibben MS Excel MVP