How to add series of rows based on quantity inputted

[kdashsohel]

Hello everyone,

I am fairly novice in terms of Macro and VBA. I work for immigration, and my supervisor had given me quite a huge task. I have a spreadsheet where I have to put information of a person's background and its family size. My problem is I am unable to figure out how to automatically create the number of rows once the family size has been inputted, also how to assign a number for each group of family created. For example below,
*(red means input, pink means automatic computerized input)*

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]John[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]John[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD]741[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Mark[/TD]
[TD]Smith[/TD]
[TD]852[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Julie[/TD]
[TD]Smith[/TD]
[TD]963[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]John[/TD]
[TD]456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]Joe[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[/TR]
</tbody>[/TABLE]
</body>
Then


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD]741[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Mark[/TD]
[TD]Smith[/TD]
[TD]852[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Julie[/TD]
[TD]Smith[/TD]
[TD]963[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]John[/TD]
[TD]456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD]741[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Mark[/TD]
[TD]Smith[/TD]
[TD]852[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Julie[/TD]
[TD]Smith[/TD]
[TD]963[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]John[/TD]
[TD]456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Then



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]# of Family[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]ID[/TD]
[TD]Family Size[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Dennis[/TD]
[TD]Smith[/TD]
[TD]123[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Smith[/TD]
[TD]741[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Mark[/TD]
[TD]Smith[/TD]
[TD]852[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Julie[/TD]
[TD]Smith[/TD]
[TD]963[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Keith[/TD]
[TD]John[/TD]
[TD]456[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Damian[/TD]
[TD]Doe[/TD]
[TD]789[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]Maryam[/TD]
[TD]Doe[/TD]
[TD]951[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am probably asking too much since I am new in this forum. Any help is greatly appreaciated.

*Note that my work environment involves two languages, English and French. I am working on an english computer station, but the majority of my colleagues are working in a french computer station. I am not sure if the VBA language will impact how excel works once shared in a different station.*

Once again thanks to anyone who is willing to take their time in helping me out!

 

[dreid1011]

Welcome to the forum.

Are the names input one at a time, and how are they added to the sheet? (manually or imported)

 

[kdashsohel]

Hello! And thank you!

The names input one at a time, but not always in an orderly fashion. To answer your second question, it is typed in manually or at times it will be copied from another sheet and pasted in this sheet.

 

[dreid1011]

Hello! And thank you!

The names input one at a time, but not always in an orderly fashion. To answer your second question, it is typed in manually or at times it will be copied from another sheet and pasted in this sheet.

Is the name that will be associated with the Family Size field input first? When is the family size input? At the same time as the name or later?

 

[kdashsohel]

The names will input first, the family size will have to input right after the names are in the cells.

 

[dreid1011]

The names will input first, the family size will have to input right after the names are in the cells.

So, all the names are put in, then family size is added after the names? At this point, you want Excel to automatically add enough rows for the rest of the family members?

 

[kdashsohel]

Correct. Lets say for example, I input 2 names, and I add a family size for one name, it creates the number of rows according to the size below that row. Then I add another family size for the other name, it creates the number of rows according to the size below its respective row etc.

 

[dreid1011]

Correct. Lets say for example, I input 2 names, and I add a family size for one name, it creates the number of rows according to the size below that row. Then I add another family size for the other name, it creates the number of rows according to the size below its respective row etc.

Ok, I think I have enough to work with now. I'll post some code back here shortly, though it may be after lunch.

 

[dreid1011]

Ok, see if this does what you want it to:

!Test this on a copy of your data first.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errX
If Not Intersect(Target, Range("F2:F1000")) Is Nothing Then
    If Target.Value > 0 Then
        Application.EnableEvents = False
        Range("A" & Target.Row + 1 & ":F" & Target.Row + 1).Resize(Target.Value - 1).Insert Shift:=xlDown
        Application.EnableEvents = True
    End If
End If
errX:
Application.EnableEvents = True
End Sub

Place this code in the sheet module for the sheet with all the names/family sizes.

Also, change the F1000 to the largest you think the range needs to be.

 

[kdashsohel]

Thank you! I'll have to test this once I resume work on Tuesday, I'll let you know if it works.