Moving rows in Macro/If statements
 

Trying to figure out the best way to use an "IF" statement in a macro that
will move data down to the next row.
Have a spreadsheet that has the following columns:
Name-Address-City-State-Child1name-Child1DOB-Child1age-Child1sex-Child2name-Child2DOB-Child2age-Child2sex-Child3name-Child3DOB-Child3age-Child3sex.
The first 4 columns will remain the same. However, if the parent has several
children, the formula/macro can identify the columns and insert them below,
matching the columns above.
So now the data should show:
Name-Address-City-State-Child1name-Child1DOB-Child1age-Child1sex
Child2name-Child2DOB-Child2age-Child2sex
Child3name-Child3DOB-Child3age-Child3sex

Moving rows in Macro/If statements
 

Here's a way to do it:

Sub Foo()
firstrow = 2 'Headings in row 1
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For r = lastrow To firstrow Step -1
Do
If Cells(r, 9 + cOff) < "" Then
rOff = rOff + 1
Rows(r + rOff).EntireRow.Insert
Cells(r, 9 + cOff).Resize(1, 4).Copy Cells(r + rOff, 5)
Cells(r, 9 + cOff).Resize(1, 4).ClearContents
cOff = cOff + 4

End If
Loop Until Cells(r, 9 + cOff) = ""
cOff = 0
rOff = 0
Next
End Sub

Regards,
Per

"Kennedy" skrev i meddelelsen
...
Trying to figure out the best way to use an "IF" statement in a macro that
will move data down to the next row.
Have a spreadsheet that has the following columns:
Name-Address-City-State-Child1name-Child1DOB-Child1age-Child1sex-Child2name-Child2DOB-Child2age-Child2sex-Child3name-Child3DOB-Child3age-Child3sex.
The first 4 columns will remain the same. However, if the parent has
several
children, the formula/macro can identify the columns and insert them
below,
matching the columns above.
So now the data should show:
Name-Address-City-State-Child1name-Child1DOB-Child1age-Child1sex

Child2name-Child2DOB-Child2age-Child2sex

Child3name-Child3DOB-Child3age-Child3sex