Note:
As written the concatenated text will be comma de-limited.
Change If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," to " " for
space delimited.
Gord
On Fri, 15 Dec 2006 11:34:15 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
Marc
CONCATENATE function will not take a range.
This User Defined Function will.
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 & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
Usage is: =ConCatRange(X1:X3)
As rows or cells are inserted betweenX1 and X3, the X1:X3 will change to
accommodate.
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.
Enter the formula as shown above.
Gord Dibben Excel MVP
On Fri, 15 Dec 2006 11:03:01 -0800, Marc Hebert <Marc
wrote:
I'm trying to construct a formula to concat text in a range of cells without
listing the cells individually in a comma-separated list, so that the formula
automatically takes into account newly inserted cells. concatenate(x1:x3)
results in a #VALUE error.
Any advice?
Thanks,
Marc
Gord Dibben MS Excel MVP