Split Cell Into Seperate Rows

[Andibevan]

I have a table of data that displays Mothers Name in Column A and Children
in a column B

Where one mother has multiple children I have been entering all the children
in a single cell, seperated by using Alt+Enter

How would I seperate the contents of the cells in column B so that each
child occupies an individual row?

Thanks

Andy

 

[Guest]

Andy,

Try this (as an example) - names in B1 are placed in cells C
onwards:


HTH

Sub x()
Dim v As Variant

v = Split(Range("b1"), Chr(10))

r = 1
For i = 0 To ubound(v)
Cells(r, i + 3) = v(i)
Next i

End Sub

 

[Andibevan]

Hey Toppers - Thanks for the great code - I was stumped.

I have managed to modify it a bit:-

It may be of use to someone else?

Sub Split_Cell_into_Rows()

Dim v As Variant
Dim rngLoc As Range
Dim rngRow As Integer
Dim rngCol As Integer
Dim RowNum As Integer 'Number of rows within source cell
Set rngLoc = Range(ActiveCell.Address) 'Location of cell
rngRow = ActiveCell.Row
rngCol = ActiveCell.Column

v = Split(rngLoc, Chr(10))
RowNum = UBound(v) '+ 1

ActiveCell.Offset(1, 0).Rows("1:" & RowNum).EntireRow.Insert
Shift:=xlDown


For i = 0 To UBound(v)
Cells(i + rngRow, rngCol) = v(i)

Next i
End Sub