Concatenate - help

[pralav]

Hi
Here is what I am trying to do
=CONCATENATE(I36:I39) I am trying to add the text in column I from 36 to 39. But when i use the above formula I am not getting the result.. I am just getting the first row back. I know we can concatenate using this =CONCATENATE(i36, i37, I38) BUT as i want to concatenate like 30 rows and very frequently i would like to have a way where i can just show the range of cells to be added. Appreciate any help and your time.

[anilsolipuram]

the formulae for range a1:a6 will be


=a1 & a2 & a3 & a4 & a5 & a6

[pralav]

Thank you for replying. I dont want to write the formula.. instead everytime i want to add bunch of cells in a row i just want to show the range so all the text will be concatenated into one cell. Please let me know if you have any idea abt it. Thank you.

[anilsolipuram]

You have to write the function for that.

copy the below function in the module in vba editor,

and use the function like

in formula like =concat_range(a1:a20)




Function concat_range(r As Range)

Dim val As Variant
Dim c As Range

For Each c In r
val = val & c.Value & " "
Next
concat_range = val
End Function

[pralav]

Thanks a lot anil,
I am trying to use the formula.. i am newbie to excel vba.. I will play with it for some time now and get back to you on that.. just to make one this sure.. I can just use this formula and select the range everytime i wanna concateneate a bunch of cells right?.. Thanks a lot again

[anilsolipuram]

I can just use this formula and select the range everytime i wanna concateneate a bunch of cells right?

Yes that is correct.


Let me know if you have any questions

[pralav]

Hey Anil
That is great.. it works beautifully.. Only thing is could you tell me how I can add code to format it? I am adding a bunch of lines and I want them to be wrapped so they all the lines i added wont be

[anilsolipuram]

In what format do you want.

[pralav]

Anil,
I just want to make sure the when I add all that string they appear clearly in that one cell. I want all the text to appear in the cell and also I dont want them to be bunched into one line. As I am adding many lines from different cells I want them to appear line by line in the same cell. Please let me know. Thank you.

pralav

[anilsolipuram]

Function concat_range(r As Range)

Dim val As Variant
Dim c As Range

For Each c In r
val = val & c.Value & Chr(10)

Next

concat_range = val


End Function