Code to convert to columns, copy and transpose data

[ianjw]

Hi

I have data in the format (two columns)

Column A Column B
a abc, xyz, ghi, klm


Assuming my starting cell is top left "a" - what I'd like to do is:

- copy the existing row into all rows below that have a blank in column A
- move to column "B"
- convert the comma separated cell into columns (comma is delimiter) - overwriting the current cell
- copy all non blank cells created in the current row(including the cell in column 2 and all cells created to the right)
- transpose and paste those cells at the current cells
- move back to column A

end result would look like this:
Column A Column B
a abc
a xyz
a ghi
a klm


Any help appreciated

Ian

[StephenR]

Can you post a sample workbook?

OK, I think this works for entries in A1 and B1, but guessing you have more to do. A workbook would still be useful.

Please Login or Register  to view this content.

[ianjw]

Here it is - my actual data has multiple rows - I already have the rows spaced with the correct number of gaps so that transposing the data will fit perfectly

So I just need code to fit inside the loop that I already have that creates the appropriate number of blank rows

[StephenR]

Sorry, I can't see how that file relates to your example.

[ianjw]

Sorry - correct attachment now added

[StephenR]

If you've got blanks in the right places, try this:

Please Login or Register  to view this content.