rowss data into a cell as comma delimited

Macro as an option.

Hi XOR,

Thanks for this idea. It works for single numbers though. I have zero idea how this is working on single numbers. However it does not give the desired result for double digits or negative numbers like -7. I have also come across another code snippet for achieving the same which i am going to share with all.

[K1] = Join(Application.Transpose(Application.Transpose(Range("A1:I1"))), ",")

So this works great for the range. A1:I1. However this works as a Macro and I am unable to make this a formula and insert it directly into the K1 cell like I could do for the code snippet graciously offered by XOR.

So can anyone help me and tell me how I may use =Join(Application.Transpose(Application.Transpose(Range("A1:I1"))), ",") as a formula / array formula and insert it directly into a cell. I tried it and get #NAME? error.

Can someone tell me how I can use [K1] = Join(Application.Transpose(Application.Transpose(Range("A1:I1"))), ",") in a macro and use a loop to increment the range A1:I1 to A2:I2 to A3:I3 and so on for the subsequent rows. I am unable to find a way / manner / format for incrementing the row numbers 1, 2 ,3 etc in the ranges. Ai:Ii does not work where i is a variable loop increment. I need something like this.

Thanks all for all the help so far.

My apologies XOR. I'll bear that in mind from now on. I do not wish to undermine your help in anyway. Thanks so much for taking the time out.

Regards.

This code snippet I found works but for one row.

[K1] = Join(Application.Transpose(Application.Transpose(Range("A1:I1"))), ",")

So this works great for the range. A1:I1. However this works as a Macro and I am unable to make this a formula and insert it directly into the K1 cell like I could do for the code snippet graciously offered by XOR.

So can anyone help me and tell me how I may use =Join(Application.Transpose(Application.Transpose(Range("A1:I1"))), ",") as a formula / array formula and insert it directly into a cell. I tried it and get #NAME? error.

Can someone tell me how I can use [K1] = Join(Application.Transpose(Application.Transpose(Range("A1:I1"))), ",") in a macro and use a loop to increment the range A1:I1 to A2:I2 to A3:I3 and so on for the subsequent rows. I am unable to find a way / manner / format for incrementing the row numbers 1, 2 ,3 etc in the ranges. Ai:Ii does not work where i is a variable loop increment. I need something like this.

Thanks all for all the help so far.

This code snippet I found works but for one row.
My code works the same as your snippet, but in all rows, not just one row.

Thanks guys for such a fantastic response. I have learnt so much. I had no clue that there was something like UDF. This has been a great learning experience. I need to digest all this and check how these work.
Yes AB33 your code works like a charm. In fact I also used it and checked for -ve numbers and double digit numbers and it is flawless. Thanks loads. I have yet to try out the UDF just suggested ProtonLeah.

Just one question still remains in my mind and i would like to ask again for clarification. Is there any way to manipulate the row-column value of Ranges like A1:J1. Can i not vary the column values to 2 or 3 and so on using a loop. Either in this form or any of the many other range forms in excel. I hope I am able to make my question clear. I ask this becos I think if these can be manipulated then i think it would be very handy. So any suggestions by anyone?

Thanks loads all ! Much obliged.

Ajoo,
The code works on any column length as long as you do not have a complete blank row or column because I used Currentregion, but can easily change if you have blanks.

Hi AB33, yes I have already discovered what you have mentioned. yes it works for any number of columns provided there is no blanks. And yes I wanted to ask what can be done if i have blocks of data separated by blanks. i want the result for each block be placed next to the top row of that block. I am very new to excel VB programming. In fact i would be very happy if you can explain how your code works. That would be so very nice and i am most grateful.

Thanks loads,
Ajoo.

Us this line

Please Login or Register  to view this content.

Adjust the column H to suite your needs

I will just try it out. Thanks. Please take some time to explain the code so that I may be able to explore it further.

Thanks !

Hi AB33 ! This works fine except that the composite output of the 2nd block is also appended to the composite of the first block. This should not happen. The combined output of each block should lie against the top row of that block. e.g as below
Block 1
ABCD H J
1234---1,2,3,4------1,2,3,4,2,3,4,5,1,2,1,2
2345---2,3,4,5
1212---1,2,1,2

Block 2
1278----1,2,7,8--------1,2,7,8,6,5,3,2,1,3,3,1
6532----6,5,3,2
1331----1,3,3,1

So like above the output of column "H" should be seperate for each block.

I hope this would be clear. Request you once again to explain the code a bit to get me started.

Thanks loads !

Hi AB33 & the rest of the gurus ! Pls help me conclude this one. Appending my last mail here once again for convenience. Would be very happy if AB33 or any guru here can explain the working of the code given by AB33. After all that's one of the main reasons most of us are here for, to learn. Thanks all !

Hi AB33 ! This works fine except that the composite output of the 2nd block is also appended to the composite of the first block. This should not happen. The combined output of each block should lie against the top row of that block. e.g as below
Block 1
ABCD H J
1234---1,2,3,4------1,2,3,4,2,3,4,5,1,2,1,2
2345---2,3,4,5
1212---1,2,1,2

Block 2
1278----1,2,7,8--------1,2,7,8,6,5,3,2,1,3,3,1
6532----6,5,3,2
1331----1,3,3,1

So like above the output of column "H" should be seperate for each block.

I hope this would be clear. Request you once again to explain the code a bit to get me started.

Thanks loads !

UDF

Use in cell like

=Joincells(A4:I4,",")

To a Standard module.

Please Login or Register  to view this content.