concatenate a large number of cells

[ROLG]

instead of listing all the cells in a column that i want to concatenat
into one cell.
(A1&A2&A3&A4&A5&A6 ETC. I HAVE TO LIST MAYBE A HUNDRED CELLS in a
column. how can i list A1 to A100???

maybe this isn't clear but--thank

 

[Paul B]

Rolg, here is a function by JE McGimpsey that should do it, if you need any
more help on how to use it just post back

Public Function MultiCat(ByRef rRng As Excel.Range, _
Optional ByVal sDelimiter As String = "") As String
'concatenate cells as a range
'From: JE McGimpsey
'Call as =MultiCat(A1:A5)
'you can add a delimiter if you wish:
'=MultiCat(A1:A5,",")

Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelimiter & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, 1 - (Len(sDelimiter) > 0))
End Function



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003