Split Names using macro

Hello Every One,
I need big help here..
I have file that may contain about 100 or more rows depending on the request. In column A, i have several names that is seperated by comma in ever row so basically what i want to do is to remove each name and insert a new column and paste the removed name in the new column and keep doing that until only one name is left for each row. I would like the code to check every row until the last row of the file and perform the splitting method that i have mentioned above. Pls. note that some row may contain only one name in this case we don't need to copy and insert new column but if the row contains more that one name then i would like to split it as shown below. I have attached file for details. I really appreciate for your help.

Here is an example of the current issue
Mike, David, John
Eric, Dave
Joe, Johnson, Edward, Ellen
Thompson, Adrienne

Here is the desired output i would like to see:
Mike
David
John
Eric
Dave
Joe
Johnson
Edward
Ellen
Thompson
Adrienne


Thank you .... Thank you so much

This may do what you wish.

Please Login or Register  to view this content.

shg,
I have tried to use the code but it is not working at all and i am not sure what is wrong with it. Could you please take a look and let me know. Thanks for your time and help.

I'm not shg. but go to the module where you installed the macro and delete the 57 out of the title Sub k57() so that it reads Sub k() and it should work for you. Otherwise it will keep jumping to cell k57 on the sheet.

Dean

The code works fine for me. Are you sure your putting it in the proper sheet you need the names separated in?

I removed the "57" and it took care one problem but i am still getting an error that reads like "Type mismatch"
I am sure it is not working for me. Pls. help. Thanks for your time and help.

The code stops at line 13. I am using Excel 2002. Thank you so much for your effort

Originally Posted by karinos57

Hello Every One,

Here is an example of the current issue
Mike, David, John
Eric, Dave
Joe, Johnson, Edward, Ellen
Thompson, Adrienne

Here is the desired output i would like to see:
Mike
David
John
Eric
Dave
Joe
Johnson
Edward
Ellen
Thompson
Adrienne


Thank you .... Thank you so much

try

Please Login or Register  to view this content.

Originally Posted by Dean

delete the 57 out of the title Sub k57() so that it reads Sub k() and it should work for you. Otherwise it will keep jumping to cell k57 on the sheet.

What makes you think that, Dean?

Originally Posted by mudraker

Please Login or Register  to view this content.

No harm, no foul, but Split always returns a zero-based array (i.e., irrespective of Option Base).

If anyone's suggested changes work for you, great. If not, please post a sample workbook that illustrates the problem, and I or someone else will help get it sorted out.

Guys, I would like first to thank you for your effort helping me out but for some reason the code fails at:
For Each e In a
or
For Each v In x

It seems that it does not pass at this line for some reason. I think it has to do with the version of Excel i am using which is 2002 and i could be wrong too.

Originally Posted by karinos57

Guys, I would like first to thank you for your effort helping me out but for some reason the code fails at:
For Each e In a
or
For Each v In x

It seems that it does not pass at this line for some reason. I think it has to do with the version of Excel i am using which is 2002 and i could be wrong too.

Thers shoud be no problem with 2002, unless you have blanks within the range.

Explain the details of the data range.

Hello karinos57,

You can use a For...Next to replace the For...Each. I made the changes below...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello karinos57,

You can use a For...Next to replace the For...Each. I made the changes below...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

1) You can't split the counter
2) You are using "v" as a counter

What is this code for?