Concatenate - help

  • Thread starter Thread starter pralav
  • Start date Start date
P

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top