Hi Kennedy,

I am refering to the first Sirname, Lewis in your example.

Due to the layout changes in this newgroup after copy - past the code
does not look as i made it.
Please try again.

Sub SplitChildern()
Dim intNumberOfKids As Integer
Dim intLoopKids As Integer
Dim intNumberLines As Integer


intNumberLines = Range(ActiveCell,
ActiveCell.End(xlDown)).Rows.Count
ActiveCell.End(xlDown).Offset(1, 0).Select
Do
ActiveCell.Offset(-1, 0).Select
intNumberOfKids = ActiveCell.End(xlToRight).Value
If intNumberOfKids 1 Then
ActiveCell.End(xlToRight).ClearContents
For intLoopKids = 1 To intNumberOfKids - 1
ActiveCell.Offset(intLoopKids, 0).EntireRow.Insert
ActiveCell.Offset(intLoopKids, 4).Value = _
ActiveCell.Offset(0, 4 + intLoopKids * 2).Value
ActiveCell.Offset(intLoopKids, 5).Value = _
ActiveCell.Offset(0, 5 + intLoopKids * 2).Value
ActiveCell.Offset(0, _
4 + intLoopKids * 2).ClearContents
ActiveCell.Offset(0, _
5 + intLoopKids * 2).ClearContents
Next
ActiveCell.End(xlToRight).Offset(0, 1).Value = _
intNumberOfKids
ActiveCell.End(xlToRight).NumberFormat = _
ActiveCell.Offset(0, 6 + intLoopKids * 2).NumberFormat
End If
intNumberLines = intNumberLines - 1
Loop Until intNumberLines = 0
End Sub


HTH,

Wouter