Adding Rows to a drop Down Menu

[Excelnoub]

I created a Validation list from 2 to 20… I want to have it (the list) add a row every time you select the proper data.

Example: If I want to add 2 row I would select the 2 in the drop down menu. If I need 5 rows I need to select 5 from the drop down menu.
Is this a VBA code? Or can I use a formula?

 

[Snakehips]

ExcelNoub,

You have to be looking at VBA.

If your DV list is in cell A2 and you want to insert your row at say existing row 3 then the following code will form the basis of what you want.

It will use the Worksheet _Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
NumRows = Target.Value
For r = 1 To NumRows
Target.Offset(2, 0).EntireRow.Insert
Next r
End Sub
 
You will need to adapt it to your particular needs and perhaps consider controling the format that your new lines attract.

Hope that helps.

 

[Excelnoub]

Thank you Snakehips, the code works perfectly.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
So the code chaged to:<o></o>
<o> </o>
<o>

[COLOR=black][FONT=Verdana]If Target.Count > 1 Then Exit Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]NumRows = Target.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For r = 1 To NumRows<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Target.Offset(1, 0).EntireRow.Insert<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next r<o:p></o:p>[/FONT][/COLOR]

</o>
So if I insert 1 or any number in C5 it will add a row or rows of the number I add in this cell. <o></o>
<o> </o>
I need this to be the same in every next line or lines after the C5 I know this is complicated to explain but here I’ll try:<o></o>
<o> </o>
If I add 4 in C5 it will add 3 rows therefore C6, C7 and C8 will be added. I don’t need to have 4 rows added, as C5 is already a row. I need the total of 3… So I need C5-C6-C7 and C8… My grid will add based on the numbers of Items in the Column C <o></o>
<o> </o>
Example: I got One box with 4 items in it and need to identify all items. So In C5 I will add 4 this will give me another 3 rows that in D5 – D6 – D7 and D8 to add the items names and in E5 – E6 – E7 and E8 to add the date of the delivery. Once this is done I need to have C9 do the same thing as this line is the next order.<o></o>

I tryied to play around but no luck, any help would be nice

 

[Snakehips]

Excelnoub,

If I am understanding correctly you want any cell in column C that has a number entered in it to generate new lines equal to one less than the number entered.

On that basis try this ....

If Target.Count > 1 Then Exit Sub
If Not Target.Column = 3 Then Exit Sub
NumRows = Target.Value-1
For r = 1 To NumRows
Target.Offset(1, 0).EntireRow.Insert
Next r

Let me know how you get on.

 

[Excelnoub]

Excelnoub,

Let me know how you get on.

I would hug you but that would be just wrong so Thank you and yes I just had to fix the 3 to a 2 To make it column 2...

You rock thanks again Snakehips

 

[Snakehips]

Excelnoub,
If you should happen to be an Excelnoubess then a hug would be more than welcome. Otherwise 'Thanks You' is perfectly acceptable.

Glad to be able to help.
Snakehips.