Need help finding a way to write a macro with an IF/OR statement.
Have a report that compiles all the data on a single row. So for each
parent, all of their data will be on the same row. At the end of the row, I
have created an IF/OR statement that counts the number of children for each
parent in that row.
I have also created a macro that will allow me to insert additional rows,
based on the number in that column at the end of the row.
Now trying to find a macro that will copy the children's information off
that row to the newly inserted rows below. Not sure if it is possible, since
the number of children vary per parent.
Currently row data looks like:
Name Address City St Child1Name Child1DOB Child2Name Child2DOB # of children
Need for it to now show:
Name Address City St Child1Name Child1DOB
# of children
Child2Name Child2DOB

Hi Kennedy,

Need just a little bit more information. Is you calculation for the # of
Children all in the same column out to the right of the record with the most
children?

If above then for families with less children are the cells between the last
child and the # of children blank?


--
Regards,

OssieMac


"Kennedy" wrote:

Need help finding a way to write a macro with an IF/OR statement.
Have a report that compiles all the data on a single row. So for each
parent, all of their data will be on the same row. At the end of the row, I
have created an IF/OR statement that counts the number of children for each
parent in that row.
I have also created a macro that will allow me to insert additional rows,
based on the number in that column at the end of the row.
Now trying to find a macro that will copy the children's information off
that row to the newly inserted rows below. Not sure if it is possible, since
the number of children vary per parent.
Currently row data looks like:
Name Address City St Child1Name Child1DOB Child2Name Child2DOB # of children
Need for it to now show:
Name Address City St Child1Name Child1DOB
# of children
Child2Name Child2DOB

OssieMac,
Thanks for the reply. Yes the #calculation is out to the right of the
information for the parent. That gives me a count of how many rows to insert
under the parent's name. Example below:
Parent Address City St Child Child Child Child
Child Child # of
Name DOB Name DOB
Name DOB Children
Lewis 123 Way Cola SC Matt 8/21 Mark 9/10 Luke 1/21 3
Webb 125 Way Cola SC Meg 2/12 Kelly 3/10 2
Peters 821 Way Cola SC Jane 11/30 Joy 6/15 Mason 4/23 3

Now need to find a way to say, for every value in that column, copy data
from cells in that row to align under the first child's name. Example"
Parent Address City St Child Child Child Child
Child Child # of
Name DOB Name DOB
Name DOB Children
Lewis 123 Way Cola SC Matt 8/21 3
Mark 9/10
Luke 1/21
Webb 125 Way Cola SC Meg 2/12 2
Kelly 3/10
Peters 821 Way Cola SC Jane 11/3 3
Joy 6/15
Mason 4/23
So for Lewis, since there are three kids, take the info from child 2 (say
columns G-K) and place under the first kid. Then take the info from child 3
(say columns L-P) and insert onto the next available row.
Honestly, I am totally lost, novice at writing macros/codes.
"OssieMac" wrote:

Hi Kennedy,

Need just a little bit more information. Is you calculation for the # of
Children all in the same column out to the right of the record with the most
children?

If above then for families with less children are the cells between the last
child and the # of children blank?


--
Regards,

OssieMac


"Kennedy" wrote:

Need help finding a way to write a macro with an IF/OR statement.
Have a report that compiles all the data on a single row. So for each
parent, all of their data will be on the same row. At the end of the row, I
have created an IF/OR statement that counts the number of children for each
parent in that row.
I have also created a macro that will allow me to insert additional rows,
based on the number in that column at the end of the row.
Now trying to find a macro that will copy the children's information off
that row to the newly inserted rows below. Not sure if it is possible, since
the number of children vary per parent.
Currently row data looks like:
Name Address City St Child1Name Child1DOB Child2Name Child2DOB # of children
Need for it to now show:
Name Address City St Child1Name Child1DOB
# of children
Child2Name Child2DOB

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

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
.

Hi again Kennedy,

I had already done some work on this prior to seeing your reply to my
questions. It now appears that you have 6 further Child columns and I have no
idea what is in them.

Anyway, I have posted code below. The professional way to manipulate data is
to not change the source data sheet but to have a source data sheet and a
destination or output data sheet.

The following code does the above. However, you need to get back to the
source data prior to counting children and inserting lines. The code provides
the required lines and counts the children. My understanding of your original
data was as follows. (I hope you kept a backup of the original data.)

Col A: Name
Col B: Address
Col C: City
Col D: St
Col E: Child1Name
Col F: Child1DOB
Col G: Child2Name
Col H: Child2DOB
Col I: Child3Name
Col J: Child3DOB
Col K: Child4Name
Col L: Child4DOB

The code follows the above with as many ChildName and Child DOB as you have.

I have tried to keep the code as generic as possible so that you can edit
the Source and Destination sheet names in only one place plus the column Id
of the first childname if it is not column E. (See my comments in the code re
this).

See my comment re optional formatting code for the number of children. This
format will display the number of children like the following.

4 Children

The formatted cells are still like normal numeric cells and the data can be
used in maths functions such as summing etc. It is only the format that is
changed like changing currency format.

Feel free to get back to me if it does not do what you want.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

If when you copy the code into you VBA editor, any of the lines are red,
then there is a line break where there should not be. You will need to edit
the code by deleting at the end of a line to bring lines together.

Sub MoveNames()
Dim wsSource As Worksheet
Dim wsDestin As Worksheet
Dim rngSource As Range
Dim c As Range
Dim tempCol As Long
Dim firstChildCol As Long
Dim parentCols As Long
Dim countChild As Long
Dim destRow As Long

'Edit "Sheet1" to name of source data sheet.
Set wsSource = Sheets("Sheet1")

'Edit "Sheet2" to name of destination data sheet.
Set wsDestin = Sheets("Sheet2")

'Edit "E" to column of first child name.
firstChildCol = wsSource.Columns("E").Column

parentCols = firstChildCol - 1

With wsSource
'Set rngSource to first column of source.
Set rngSource = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))

End With

For Each c In rngSource.Rows
With wsSource
tempCol = firstChildCol

'Find next destination parent row
'after last data in first child column
With wsDestin
destRow = .Cells(.Rows.Count, tempCol) _
.End(xlUp).Offset(1, 0).Row
End With

'Copy and paste parent data
.Range(c, c.Offset(0, parentCols - 1)).Copy _
wsDestin.Cells(destRow, "A")

'Copy each child name,DOB and count children
countChild = 0
Do
If .Cells(c.Row, tempCol) < "" Then
.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 1)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1
tempCol = tempCol + 2
Else
Exit Do
End If

Loop

'Insert child count
With wsDestin
'Following format line is optional.
.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) _
.NumberFormat = "0"" Children"""

.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) = countChild

End With
End With
Next c
wsDestin.Columns.AutoFit

wsDestin.Select
End Sub

--
Regards,

OssieMac

Hi OssieMan,
I think I need to bow down to you and kiss your feet. The code you gave me
worked great. Just wondering, if I wanted to add more info to the children
section, such as age, sex, or three more additional files, how do I do that?
Again, I am such a novice a this, that my macros are created by actually
running the steps and performing the actions.
Also, when I did the code you wrote and pulled it into a seperate page, it
added the number of children at the bottom. How do I remove that, since it is
not important?

Also, do you charge to do side work? Have another report that I am working
on that I may need help with. The format is similar, but the type of data is
different. Would take me years to even figure it out, or how to modify the
code to work with. Let me know if you are interested,


"OssieMac" wrote:

Hi again Kennedy,

I had already done some work on this prior to seeing your reply to my
questions. It now appears that you have 6 further Child columns and I have no
idea what is in them.

Anyway, I have posted code below. The professional way to manipulate data is
to not change the source data sheet but to have a source data sheet and a
destination or output data sheet.

The following code does the above. However, you need to get back to the
source data prior to counting children and inserting lines. The code provides
the required lines and counts the children. My understanding of your original
data was as follows. (I hope you kept a backup of the original data.)

Col A: Name
Col B: Address
Col C: City
Col D: St
Col E: Child1Name
Col F: Child1DOB
Col G: Child2Name
Col H: Child2DOB
Col I: Child3Name
Col J: Child3DOB
Col K: Child4Name
Col L: Child4DOB

The code follows the above with as many ChildName and Child DOB as you have.

I have tried to keep the code as generic as possible so that you can edit
the Source and Destination sheet names in only one place plus the column Id
of the first childname if it is not column E. (See my comments in the code re
this).

See my comment re optional formatting code for the number of children. This
format will display the number of children like the following.

4 Children

The formatted cells are still like normal numeric cells and the data can be
used in maths functions such as summing etc. It is only the format that is
changed like changing currency format.

Feel free to get back to me if it does not do what you want.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

If when you copy the code into you VBA editor, any of the lines are red,
then there is a line break where there should not be. You will need to edit
the code by deleting at the end of a line to bring lines together.

Sub MoveNames()
Dim wsSource As Worksheet
Dim wsDestin As Worksheet
Dim rngSource As Range
Dim c As Range
Dim tempCol As Long
Dim firstChildCol As Long
Dim parentCols As Long
Dim countChild As Long
Dim destRow As Long

'Edit "Sheet1" to name of source data sheet.
Set wsSource = Sheets("Sheet1")

'Edit "Sheet2" to name of destination data sheet.
Set wsDestin = Sheets("Sheet2")

'Edit "E" to column of first child name.
firstChildCol = wsSource.Columns("E").Column

parentCols = firstChildCol - 1

With wsSource
'Set rngSource to first column of source.
Set rngSource = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))

End With

For Each c In rngSource.Rows
With wsSource
tempCol = firstChildCol

'Find next destination parent row
'after last data in first child column
With wsDestin
destRow = .Cells(.Rows.Count, tempCol) _
.End(xlUp).Offset(1, 0).Row
End With

'Copy and paste parent data
.Range(c, c.Offset(0, parentCols - 1)).Copy _
wsDestin.Cells(destRow, "A")

'Copy each child name,DOB and count children
countChild = 0
Do
If .Cells(c.Row, tempCol) < "" Then
.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 1)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1
tempCol = tempCol + 2
Else
Exit Do
End If

Loop

'Insert child count
With wsDestin
'Following format line is optional.
.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) _
.NumberFormat = "0"" Children"""

.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) = countChild

End With
End With
Next c
wsDestin.Columns.AutoFit

wsDestin.Select
End Sub

--
Regards,

OssieMac

OssieMac,
Sorry, found a way to remove the count after I sent the note to you. So
thanks.
However, still curious as to how to add additional columns behind the childs
name that will allow me to pull their information in as well. May need to add
more info like age, sex, phone number etc and pull that data as well.
Probably something simple, but being the novice that I am, difficult for me.

"OssieMac" wrote:

Hi again Kennedy,

I had already done some work on this prior to seeing your reply to my
questions. It now appears that you have 6 further Child columns and I have no
idea what is in them.

Anyway, I have posted code below. The professional way to manipulate data is
to not change the source data sheet but to have a source data sheet and a
destination or output data sheet.

The following code does the above. However, you need to get back to the
source data prior to counting children and inserting lines. The code provides
the required lines and counts the children. My understanding of your original
data was as follows. (I hope you kept a backup of the original data.)

Col A: Name
Col B: Address
Col C: City
Col D: St
Col E: Child1Name
Col F: Child1DOB
Col G: Child2Name
Col H: Child2DOB
Col I: Child3Name
Col J: Child3DOB
Col K: Child4Name
Col L: Child4DOB

The code follows the above with as many ChildName and Child DOB as you have.

I have tried to keep the code as generic as possible so that you can edit
the Source and Destination sheet names in only one place plus the column Id
of the first childname if it is not column E. (See my comments in the code re
this).

See my comment re optional formatting code for the number of children. This
format will display the number of children like the following.

4 Children

The formatted cells are still like normal numeric cells and the data can be
used in maths functions such as summing etc. It is only the format that is
changed like changing currency format.

Feel free to get back to me if it does not do what you want.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

If when you copy the code into you VBA editor, any of the lines are red,
then there is a line break where there should not be. You will need to edit
the code by deleting at the end of a line to bring lines together.

Sub MoveNames()
Dim wsSource As Worksheet
Dim wsDestin As Worksheet
Dim rngSource As Range
Dim c As Range
Dim tempCol As Long
Dim firstChildCol As Long
Dim parentCols As Long
Dim countChild As Long
Dim destRow As Long

'Edit "Sheet1" to name of source data sheet.
Set wsSource = Sheets("Sheet1")

'Edit "Sheet2" to name of destination data sheet.
Set wsDestin = Sheets("Sheet2")

'Edit "E" to column of first child name.
firstChildCol = wsSource.Columns("E").Column

parentCols = firstChildCol - 1

With wsSource
'Set rngSource to first column of source.
Set rngSource = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))

End With

For Each c In rngSource.Rows
With wsSource
tempCol = firstChildCol

'Find next destination parent row
'after last data in first child column
With wsDestin
destRow = .Cells(.Rows.Count, tempCol) _
.End(xlUp).Offset(1, 0).Row
End With

'Copy and paste parent data
.Range(c, c.Offset(0, parentCols - 1)).Copy _
wsDestin.Cells(destRow, "A")

'Copy each child name,DOB and count children
countChild = 0
Do
If .Cells(c.Row, tempCol) < "" Then
.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 1)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1
tempCol = tempCol + 2
Else
Exit Do
End If

Loop

'Insert child count
With wsDestin
'Following format line is optional.
.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) _
.NumberFormat = "0"" Children"""

.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) = countChild

End With
End With
Next c
wsDestin.Columns.AutoFit

wsDestin.Select
End Sub

--
Regards,

OssieMac

Hi OssieMac,
Wanted to personally thank you for your help with this. Something that I
thought was impossible is actually working now. Still a few bugs, but hey,
better then what I previously was going to attempt to do. I am just amazed at
how quickly you came up with the code...unbelievable.
One quick question though, in the code I change one section to get the
additional columns that I needed:

'Copy each child name,DOB and count children
countChild = 0
Do
If .Cells(c.Row, tempCol) < "" Then
.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 6)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1
tempCol = tempCol + 7
Else
Exit Do
End If

This works perfect for what I need. BUt just wondering what section do I
need to change if the child's name is not present, but the other information
is there. We have situations when the data is sent over that the child's name
is missing, however we have the DOB, Sex, Age, Grad populated. In the code
that you shared, it will automatically weed those out. So while the parent
may have 5 kids, with 4 kids information fully populated. The fifth kid whose
name is missing but other data is populated does not show up. Still want the
rest of their information in the report so we can go back and fill in that
data later.
Again, THANK YOU...I owe you a drink or two. LOL
Kennedy

"OssieMac" wrote:

Hi again Kennedy,

I had already done some work on this prior to seeing your reply to my
questions. It now appears that you have 6 further Child columns and I have no
idea what is in them.

Anyway, I have posted code below. The professional way to manipulate data is
to not change the source data sheet but to have a source data sheet and a
destination or output data sheet.

The following code does the above. However, you need to get back to the
source data prior to counting children and inserting lines. The code provides
the required lines and counts the children. My understanding of your original
data was as follows. (I hope you kept a backup of the original data.)

Col A: Name
Col B: Address
Col C: City
Col D: St
Col E: Child1Name
Col F: Child1DOB
Col G: Child2Name
Col H: Child2DOB
Col I: Child3Name
Col J: Child3DOB
Col K: Child4Name
Col L: Child4DOB

The code follows the above with as many ChildName and Child DOB as you have.

I have tried to keep the code as generic as possible so that you can edit
the Source and Destination sheet names in only one place plus the column Id
of the first childname if it is not column E. (See my comments in the code re
this).

See my comment re optional formatting code for the number of children. This
format will display the number of children like the following.

4 Children

The formatted cells are still like normal numeric cells and the data can be
used in maths functions such as summing etc. It is only the format that is
changed like changing currency format.

Feel free to get back to me if it does not do what you want.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

If when you copy the code into you VBA editor, any of the lines are red,
then there is a line break where there should not be. You will need to edit
the code by deleting at the end of a line to bring lines together.

Sub MoveNames()
Dim wsSource As Worksheet
Dim wsDestin As Worksheet
Dim rngSource As Range
Dim c As Range
Dim tempCol As Long
Dim firstChildCol As Long
Dim parentCols As Long
Dim countChild As Long
Dim destRow As Long

'Edit "Sheet1" to name of source data sheet.
Set wsSource = Sheets("Sheet1")

'Edit "Sheet2" to name of destination data sheet.
Set wsDestin = Sheets("Sheet2")

'Edit "E" to column of first child name.
firstChildCol = wsSource.Columns("E").Column

parentCols = firstChildCol - 1

With wsSource
'Set rngSource to first column of source.
Set rngSource = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))

End With

For Each c In rngSource.Rows
With wsSource
tempCol = firstChildCol

'Find next destination parent row
'after last data in first child column
With wsDestin
destRow = .Cells(.Rows.Count, tempCol) _
.End(xlUp).Offset(1, 0).Row
End With

'Copy and paste parent data
.Range(c, c.Offset(0, parentCols - 1)).Copy _
wsDestin.Cells(destRow, "A")

'Copy each child name,DOB and count children
countChild = 0
Do
If .Cells(c.Row, tempCol) < "" Then
.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 1)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1
tempCol = tempCol + 2
Else
Exit Do
End If

Loop

'Insert child count
With wsDestin
'Following format line is optional.
.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) _
.NumberFormat = "0"" Children"""

.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) = countChild

End With
End With
Next c
wsDestin.Columns.AutoFit

wsDestin.Select
End Sub

--
Regards,

OssieMac

Hi Kennedy,

Sorry it has taken so long to get back to you but we are probably in
different time zones.

This is purely a hobby for an ageing retiree and I don't take on the
responsibility of paid work. I have the odd (and sometimes frequent) Seniors
Moment but generally I enjoy the challenge and it keeps my mind active.

The following code tests for additional cells with data to the right of
tempCol. If you have additional data out to the right that you do not want
included then we have the option of getting rid of it off the Source data
sheet or we will have to re-think the options.

Then there is the problem of the blank child name cell. The code relies on
there being data in the child name to find the next row on the destination
sheet. Therefore I have tested for data to the right of tempCol and if exists
then test tempCol for data and if empty then insert dummy data on the source
worksheet. I have used TBA (To Be Advised).


Do
'Test if any more data on the row
'between tempCol and far right of sheet.
If WorksheetFunction. _
CountA(.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, .Columns.Count))) 0 Then

'Insert character/s into blank cells
If .Cells(c.Row, tempCol) = "" Then
'Edit "TBA" to whatever you like
.Cells(c.Row, tempCol) = "TBA"
End If

.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 6)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1 'Remove if not required
tempCol = tempCol + 7
Else
Exit Do
End If

Loop


Now the entire section shown below can be removed to remove the child count.

'Insert child count
With wsDestin
'Following format line is optional.
.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) _
.NumberFormat = "0"" Children"""

.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) = countChild

End With

--
Regards,

OssieMac

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