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)*

#	# of Family	First Name	Last Name	ID	Family Size
1		Dennis	Smith	123	4
2		Keith	John	456
3		Damian	Doe	789

<tbody>
</tbody>

Then

#	# of Family	First Name	Last Name	ID	Family Size
1	1	Dennis	Smith	123	4
2	1
3	1
4	1
5		Keith	John	456
6		Damian	Doe	789

<tbody>
</tbody>

Then

#	# of Family	First Name	Last Name	ID	Family Size
1	1	Dennis	Smith	123	4
2	1	Jane	Smith	741
3	1	Mark	Smith	852
4	1	Julie	Smith	963
5		Keith	John	456	1
6		Damian	Doe	789

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

#	# of Family	First Name	Last Name	ID	Family Size
1	1	Dennis	Smith	123	4
2	1
3	1
4	1
5		Keith	Joe	456
6		Damian	Doe	789

<tbody>
</tbody>

</body>
Then

#	# of Family	First Name	Last Name	ID	Family Size
1	1	Dennis	Smith	123	4
2	1	Jane	Smith	741
3	1	Mark	Smith	852
4	1	Julie	Smith	963
5		Keith	John	456	1
6		Damian	Doe	789

<tbody>
</tbody>

Then

#	# of Family	First Name	Last Name	ID	Family Size
1	1	Dennis	Smith	123	4
2	1	Jane	Smith	741
3	1	Mark	Smith	852
4	1	Julie	Smith	963
5		Keith	John	456	1
6		Damian	Doe	789	2

<tbody>
</tbody>

Then

#	# of Family	First Name	Last Name	ID	Family Size
1	1	Dennis	Smith	123	4
2	1	Jane	Smith	741
3	1	Mark	Smith	852
4	1	Julie	Smith	963
5		Keith	John	456	1
6	2	Damian	Doe	789	2
7	2	Maryam	Doe	951

<tbody>
</tbody>

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.

Number

Date of Inland

Time of Inland

Date application submtted at the POE

File number

LAST NAME

First name

HOF

HOF number

ID

Citizenship country

Country of birth

Language for interpretation

1st official encountered at the border

Province of destination

Not accompanied minor

Referred to internally

Case processing

Decision

Deferred to investigation

AGENT

VISA USA

VISA OBTAINED

PDE AT USA

Ref Claim in US?

US Ref Claim result?

Interpreter Required

Official Language

Date of first contact

Date of case completed

Number of person in family

Number of Family

Family Size

<tbody>
</tbody>

 

[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.