Is it possible to concatenate a range of data??

Posted by Phil Atio on January 31, 2002 7:07 AM

I have tried =Concatenate(A1:A10) but it doesnt work.
I dont want to have to type a long concatenation query and will be adding a new number to the column each week, which I want to be automatically added to the concatenated field.

Can anyone help please?

thanks

Posted by DK on January 31, 2002 7:26 AM

Hello Mate,

I don't know of a way to do this with a formula. What I do is use this simple VBA function. To use it, open the VB Editor (Alt+F11), click Insert, Module and then paste this.

Function SpecialConcat(rnge As Range, Optional Seperator As String)
Dim lr As Long, lc As Long

For lr = 1 To rnge.Rows.Count
For lc = 1 To rnge.Columns.Count
SpecialConcat = SpecialConcat & rnge.Cells(lr, lc) & Seperator
Next lc
Next lr
SpecialConcat = Left(SpecialConcat, Len(SpecialConcat) - Len(Seperator))
End Function

If you wanted to have use of this in any workbook you could put it in your personal macro workbook or put it in an add-in.

HTH,
D

Posted by jonh on January 31, 2002 8:40 AM

That is a very cool macro, but I am only familiar with th emacro recorder. I have followed your instructions, but don't know how to get the function to work. Could you cover that a bit?

Posted by Phil Atio on February 01, 2002 12:14 AM

Thanks very much (nt)

Posted by Phil Atio on February 01, 2002 12:38 AM

To get the function to work, click the function button and find the 'USER DEFINED' range of functions. You should find it in there and you can use it the same way as any other!
And it works!!

Posted by jonh on February 01, 2002 11:40 AM

Posted by jonh on February 01, 2002 11:41 AM

Very nice, thanks!!

Ver