Adding Rows to a drop Down Menu

Excelnoub

Board Regular
Joined
Nov 17, 2011
Messages
230
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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

Board Regular
Joined
Nov 17, 2011
Messages
230

Thank you Snakehips, the code works perfectly.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
So the code chaged to:<o:p></o:p>
<o:p> </o:p>
<o:p>
Code:
[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:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>

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

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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 ....

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

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,045
Messages
5,599,488
Members
414,313
Latest member
Oonagh123

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