Concatenate - help

[pralav]

Hi
I am trying to concatenate a set of rows in one column. I have the
eqation for concatenating couple of rows.. can you please tell me how
to concatenate more than couple of rows by just selecting the rows. I
would have to do this repeatedly for a large file.. appreciate 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.. instea
everytime i want to add bunch of cells in a row i just want to show th
range so all the text will be concatenated into one cell. Please let m
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 Functio

 

[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 m
how I can add code to format it? I am adding a bunch of lines and
want them to be wrapped so they all the lines i added wont b

 

[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