Split cell into multiple rows

[lando99]

How I can split a cell into multiple rows and delete the original row? My data looks like this: 4 columns with the last column having multiple data separated by Alt-Enter

A1 B1 C1 V1
V2
V3
--------------------
A2 B2 C2 V1
V2

I want it to look like this: separate rows with same data in first 3 columns.

A1 B1 C1 V1
A1 B1 C1 V2
A1 B1 C1 V3
A2 B2 C2 V1
A2 B2 C2 V2

Thank you for any help you can provide.

[mike7952]

Can you upload your workbook that will show what your data looks like before and what you would like it to look after.

[lando99]

I attached the file to show the layout of my data and what I would like it to look like after. thanks.

[mike7952]

Try this results will be added to sheet 2

Please Login or Register  to view this content.

[Winon]

@ mike7952,

It is so unfair!

Your Code works a treat. But I don't understand 89.9% of what it actually means.

"CountLarge, Ubound, LBound, Redims, If s(ii), Preserve,vbLf, etc."

,, LOL

[lando99]

Your code does the trick and got the results that I needed. Thanks for your help!

[lando99]

When I executed this same code on my 'real' data, it gives me an error, and I'm not sure how to fix it. Attached is the data that I'm trying to run the code against.

[mike7952]

Give this a try. It was wanting to add the data to "Sheets2" and the workbook does not have a sheet name sheet2. I changed the code to add a sheet.

Please Login or Register  to view this content.

[lando99]

Thanks again Mike.

I have another question. What if I have more than 4 columns... for example, if I have 6 columns with similar setup as the one in my example, the last column (column 6)being the one I want to split, what would I need to change in your code to run against 6 columns of data? This will help me understand what I can modify to make it work for different size tables that I might have. Thank you for your help.

[mike7952]

I have add some code so that as long as the column to split is in the last column to the right all you will nee to do is add just a few lines. I added some comments. Let me know if you still need some help.

Please Login or Register  to view this content.

[lando99]

I tried to test against a 10-columns data and I received a 'type mismatch' error at the transpose function. Attached is the data that I ran against and the changes to the macro as you suggested. Also, is there a limit on how many rows I can have? Thanks.

[mike7952]

You will nee to add the Left(value,255) function to all of these below if the cell will contain more the 255 chars.
Cell C10 has a len of 533 chars.

Please Login or Register  to view this content.

[mike7952]

If that a big issue I'll have to rewrite the code. Transpose will not handle more the 255 chars.

[jindon]

Try this one, no need to cut off the string.

Please Login or Register  to view this content.

[mike7952]

@Jindon

Sweet code, I tested it on the workbook from Post #7 and its not picking up the lastrow. I dont know why?

[lando99]

Adding Left(value,255) works for me. I only have a few cells that have >255 chars so no big deal. Thanks so much for your help.