How to add series of rows based on quantity inputted

kdashsohel

New Member
Joined
Jul 27, 2017
Messages
9
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 FamilyFirst NameLast NameIDFamily Size
1DennisSmith1234
2KeithJohn456
3DamianDoe789

<tbody>
</tbody>

Then

## of FamilyFirst NameLast NameIDFamily Size
11DennisSmith1234
21
31
41
5KeithJohn456
6DamianDoe789

<tbody>
</tbody>

Then

## of FamilyFirst NameLast NameIDFamily Size
11DennisSmith1234
21JaneSmith741
31MarkSmith852
41JulieSmith963
5KeithJohn4561
6DamianDoe789

<tbody>
</tbody>
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
## of FamilyFirst NameLast NameIDFamily Size
11DennisSmith1234
21
31
41
5KeithJoe456
6DamianDoe789

<tbody>
</tbody>
</body>
Then


## of FamilyFirst NameLast NameIDFamily Size
11DennisSmith1234
21JaneSmith741
31MarkSmith852
41JulieSmith963
5KeithJohn4561
6DamianDoe789

<tbody>
</tbody>

Then

## of FamilyFirst NameLast NameIDFamily Size
11DennisSmith1234
21JaneSmith741
31MarkSmith852
41JulieSmith963
5KeithJohn4561
6DamianDoe7892

<tbody>
</tbody>

Then



## of FamilyFirst NameLast NameIDFamily Size
11DennisSmith1234
21JaneSmith741
31MarkSmith852
41JulieSmith963
5KeithJohn4561
62DamianDoe7892
72MaryamDoe951

<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!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
Welcome to the forum.

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

kdashsohel

New Member
Joined
Jul 27, 2017
Messages
9
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

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
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

New Member
Joined
Jul 27, 2017
Messages
9

ADVERTISEMENT

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

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
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

New Member
Joined
Jul 27, 2017
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
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

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
Ok, see if this does what you want it to:

!Test this on a copy of your data first.

Code:
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.
 
Last edited:

kdashsohel

New Member
Joined
Jul 27, 2017
Messages
9
Thank you! I'll have to test this once I resume work on Tuesday, I'll let you know if it works.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top