Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

[jamilm]

very nice. i was looking for this code. coincidentally, i searched before posting the question in Mrexcel forum and here i got exactly what i needed.

thank you Rick and Marcelo.

[kristofpoppe]

Nice piece of code, although I see a problem, when a value is blank in another collumn, it is copied from the cell that contains a value, so it filles other empty cells with bogus data

Eg.

AA BBB 1:2
CC 2:3

Will become:

AA BBB 1
AA BBB 2
CC BBB 2
CC BBB 3

Any solution for this ?