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!

 

[kdashsohel]

Hello! Back in office, I tested this code and it worked! I understand this works on a basic test sheet, how can I put this on a more complex spreadsheet I have that consists of multiple different columns but 6 of the columns mentioned above is included in my spreadsheet.

 

[kdashsohel]

These are the names of the columns I have in my spreadsheet.

[TABLE="width: 4878"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Date of Inland [/TD]
[TD]Time of Inland [/TD]
[TD] Date application submtted at the POE[/TD]
[TD]File number[/TD]
[TD]LAST NAME[/TD]
[TD]First name[/TD]
[TD]HOF[/TD]
[TD]HOF number[/TD]
[TD]ID[/TD]
[TD]Citizenship country[/TD]
[TD]Country of birth[/TD]
[TD]Language for interpretation[/TD]
[TD]1st official encountered at the border[/TD]
[TD]Province of destination[/TD]
[TD]Not accompanied minor[/TD]
[TD] Referred to internally[/TD]
[TD]Case processing [/TD]
[TD]Decision[/TD]
[TD] Deferred to investigation [/TD]
[TD]AGENT[/TD]
[TD]VISA USA[/TD]
[TD]VISA OBTAINED[/TD]
[TD]PDE AT USA[/TD]
[TD]Ref Claim in US?[/TD]
[TD]US Ref Claim result?[/TD]
[TD]Interpreter Required[/TD]
[TD]Official Language[/TD]
[TD]Date of first contact[/TD]
[TD]Date of case completed[/TD]
[TD]Number of person in family[/TD]
[TD]Number of Family[/TD]
[TD]Family Size[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

This should do it assuming your last column is AG and the Family Size (AG) is the only column that this needs to work on. Also, remember to change the AG1000 to however long you think the sheet might be.

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

 

[kdashsohel]

I've tried this, and it seems that it works. However, for example I added 2 for the family size, it only adds 2 extra rows across only 6 columns (A-F), not across the entire column I have in the spreadsheet (A-AG).

 

[dreid1011]

I've tried this, and it seems that it works. However, for example I added 2 for the family size, it only adds 2 extra rows across only 6 columns (A-F), not across the entire column I have in the spreadsheet (A-AG).

Did you make the adjustments yourself or did you copy the new code in post #13 ? You need to change this part too:

Range("A" & Target.Row + 1 & ":F" & Target.Row + 1).Resize(Target.Value - 1).Insert Shift:=xlDown

to

Range("A" & Target.Row + 1 & ":AG" & Target.Row + 1).Resize(Target.Value - 1).Insert Shift:=xlDown

 

[kdashsohel]

Thank you very much for taking the time to reply me with patience. It is now working, I cannot express my gratitude enough for this.

 

[dreid1011]

Thank you very much for taking the time to reply me with patience. It is now working, I cannot express my gratitude enough for this.

You're very welcome. I'm glad it's working for you.