Thanks Wouter for the reply.
Copied the macro into spreadsheet, using excel2003.
When you say select cell with first name, I assume you mean the cell
containing the name Matt, in my case.
Tried to run and received a Compile error: Syntax error

"Wouter HM" wrote:

Hi Kennedy,

Using Excel 2007 I created this macro:
Before you start it, select the cell with the first name.

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
.