Need Macro To Create New Rows Based on Comma Separted Values in Cell

**alex20850x** · 03-28-2016, 02:47 PM

I will start with a spreadsheet where one or more of the columns has commas in the cells.
I want to be asked which column has the values that will be broken out into new rows.
Any other cells in the same row should be repeated for the new rows.
In this case, it is column B, but it can vary depending on the spreadsheet.

Before
A B
Tommy Student
Harry Bartender, Video store clerk
Sally Waitress

After
A B
Tommy Student
Harry Bartender
Harry Video store clerk
Sally Waitress[/B]

**Jack7774** · 03-28-2016, 03:15 PM

This won't work for a cell value that has two or more commas though.

Please Login or Register  to view this content.

**alex20850x** · 03-28-2016, 03:28 PM

Thanks, but my data has more than two commas.

**walruseggman** · 03-28-2016, 03:31 PM

This will work with any number of commas. You can input a column letter or number, doesn't matter. Also assumed your data has a comma followed by a space as your example shows, not just commas.

E.g. A, B

not

A,B

Please Login or Register  to view this content.

**alex20850x** · 03-28-2016, 03:42 PM

Thanks, looks good, but when I ran it, I got "Variable not defined."

**alex20850x** · 03-28-2016, 03:48 PM

I added the Dim statements and it works great.
One last request. How easy would it be to have the data to a new sheet.

**walruseggman** · 03-28-2016, 04:00 PM

Ha, small world, I grew up south of Davenport.

For new sheet:

Please Login or Register  to view this content.

**alex20850x** · 03-28-2016, 04:10 PM

I get sheet named Output and a number, but no data.

Nice state. Moved here in 2013 from Washington DC. No traffic...yeah!

**walruseggman** · 03-28-2016, 04:14 PM

Can you post a sample workbook that it's not working on? It works as expected for me on your original worksheet you posted.

**alex20850x** · 03-28-2016, 05:13 PM

I don't know what I was doing wrong, but now it works.
I suspect I had an extra row above the data and that was throwing it off.

Thanks again. BRAVO!!

**walruseggman** · 03-28-2016, 05:15 PM

Glad to help. Please mark this thread SOLVED, and any reputation points (the * in the lower left of the posts) are appreciated.

Need Macro To Create New Rows Based on Comma Separted Values in Cell

LinkBack

Thread Tools

Rate This Thread

Display

Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Re: Need Macro To Create New Rows Based on Comma Separted Values in Cell

Thread Information

Users Browsing this Thread

Similar Threads

Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to Rows

Macro to create a list of sheets, separated with comma in a single cell

[SOLVED] Macro to Create Message box based on cell values

Create Auto-Calculating Cell and Assign Cell Values Based on a Macro

[SOLVED] Splitting cell contents based on Comma and inserting new rows below it.

need macro to create multiple rows based on cell qty

Comma separted values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions