splitting one cell into multiple rows (and retaining all other data from other columns)

I am not sure what you are trying to do, but try this on a copy of your file or a mock up and see if it does what you want.

Please Login or Register  to view this content.

Thank you very much for the response - this is definitely taking me in the right direction! I'm not so great with the VB code at times, and the ability to insert an entire row is new to me.
Again, thank you. Basically, you have the right idea of what I want to achieve but there is one thing missing. See below image for reference.

cells to split 2.jpg

I also wanted to copy all the other values in the other columns for that row into the new row once the data is split. So wherever data in column D (in this screenshot) or any of the other columns exist, I wanted that to be copied down into the newly created rows. Is that possible? Would that be including something additional in the VB code you provided before?

Thank you again.

I thought you might want that, but wanted to make sure I understood the OP first.

Please Login or Register  to view this content.

Thank you!!! This is exactly what I needed and thoroughly appreciate your help as I wouldn't have been able to figure some of this out without extra help. I am typically working with this data in a PivotTable and this is why I needed the data like this which is easier to work with.

I do have one final additional question as scenarios can come up where I have data like what was in column E also in other columns that need to be split and "matched up" with respective data from the other columns. For example:

cells to split 3.jpg

How would I modify the last set of VB code that you provided in order to also parse out additional columns. In this example, this would be columns E, F, and G. Again, what is in column E needs to be matched with what was exported in F and G from the system that outputs this. This is probably far more complicated, but if it is possible, I'd really appreciate the direction as this may come up at some point.

Major thanks again for your assistance and expertise!

It would be easier to work with if you would upload your file or a facimile of the file. You can do that by clicking 'Go Advanced' at the bottom of the reply input box, Then click 'Manage Attachments' and follow the options menu. In general, the Split function creates an array and you can use either the comma (","r Space (" ") as delimiters, based on how the data is entered into the cells. Then to make it paste vertically, use the Application.Transpose function. The array is zero based so LBound - 0 and UBound will = one less than the number of items in the array. The 'For i' statement then uses the LBound and UBound integer values to set the number of rows by resizing from and anchor cell or range. It is all a matter of simple math once you understand where the numbers come from and how to use them with the Offset and Resize methods..

From the data provided, it appears that there will always be the same number of items in each cell for columns B, C and D. If this holds true, then only one cell per row will need to be used for numerical purposes of Offset and Resizing to add rows and populate them.. If there was a disparity in the count of items between the three columns using arrays, then it would be more complex.

Please Login or Register  to view this content.

This takes a few seconds to run because of the looping, but it should not take more than a minute.