How to insert no. of rows under main rows according to no. of family members....?

[Indra Rai]

Dear All,

I want to insert rows under main rows according to no. of family member along with main rows at once. Eg. No. of family members are 5 then I want to insert more 4 rows under main rows. How can I do within huge document? I will be highly appreciated if you suggest me. The sample file is also attached.


Thanks.

Indra

[sktneer]

You may try this......

Sub InsertBlankRows()
Dim rng As Range, cell As Range
Dim lr As Long, flr As Long
lr = Cells(Rows.Count, 7).End(xlUp).Row
Set rng = Range("G4:G" & lr)
Application.ScreenUpdating = False
For Each cell In rng
    If cell <> "" Then
        cell.Offset(1, 0).Resize(cell - 1, 1).EntireRow.Insert
        cell.EntireRow.Copy
        cell.Offset(1, 0).Resize(cell - 1, 1).EntireRow.PasteSpecial xlPasteFormats
    End If
Next cell
Range("A3").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Please find the attached and click on the Green Button on Test Sheet to see if this works as per your requirement.

[Indra Rai]

Dear sktneer,

Thanks for your kind help and brilliant mind.

Indra

[sktneer]

You're welcome. Glad I could help.
Moreover you may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks.