Insert new rows based on user input

R

remmuS24

New Member
Joined
Apr 27, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Not sure if this is possible, or just wishful thinking: Is there way using either VBA or a formula for a user to manually enter the number of rows required and have excel to extend the rows to meet that number? In the attached image attached, there are 2 user input fields: the first at E17, and the second at I17. E17 would control how many rows should be appearing for example between the current rows 21:25 (and copy through the formulas as well). I17 would control how many rows should be appearing for example between the current rows 27:31 (while still copying the formulas as well). I'm pretty sure I've seen this somewhere before, but can't find it.

The harder part to my question is, is there a way for those same user inputs to also expand the number of rows in additional areas? For example, to also include new rows between 37:41, and 42:46, and then 72:76 and 77:81? The thought/goal is for the user to insert the required number of walls/rows required for design, and then have the sheet populate the required number of rows in each calc section to then size the walls without the user having to manually copy the solved rows and then do a "insert copied cells" multiple times in each section until they get the correct number of rows.
 

Attachments

  • post question.png
    post question.png
    86.6 KB · Views: 47

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hello remmuS24, you can do this with a macro, but the sheet needs to be modified a little. You're saying you have 6 sections, and the number of rows will vary depending on how many rows need to be entered. but that is okay, here is how i would handle it. assume your current sheet is your template. you have to design a template so there is a starting point. so, in this template, in cells aa21, aa37, aa72, enter =$b$17. in cells ab21, ab37, ab72, enter =$e$17. be sure to save your template. Cheers!
VBA Code: 
''' Press F8 to see what the code does line-by-line

Sub macro1_all()
    Application.Run "macro3"
    Application.Run "macro4"
End Sub

Sub Macro3()
'cop AA AB to AC AD as values
    Application.Goto Reference:="R1C27"
    Columns("AA:AB").Select
    Selection.Copy
    Application.Goto Reference:="R1C29"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


Sub Macro4()
    On Error GoTo Err_Handler

For i = 1 To 444
    
'''go to AC1, XLDown to first non-blank cell, copy the cell next to it, in AD, paste it to AD1
    Application.Goto Reference:="R1C29"
    Selection.End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Application.Goto Reference:="R1C30"
    ActiveSheet.Paste
    Selection.Copy
    
'''go back to AC1, find first non-blank cell again
    Selection.Copy
    Application.Goto Reference:="R1C29"
    Selection.Copy
    Selection.End(xlDown).Select
    Selection.Copy
    
'''go to column A
    ActiveCell.Offset(0, -28).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy

''''select the number you entered, and insert these number of rows
    ActiveCell.Range("A1:AE" & Range("AD1")).Select
    Selection.EntireRow.Insert
    Selection.Copy
    
    
'''go back to AC1, find first non-blank cell again
    Selection.Copy
    Application.Goto Reference:="R1C29"
    Selection.End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(0, -28).Range("A1").Select
    Selection.Copy
'''select A to Z to copy your formula
    ActiveCell.Range("A1:Z1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Selection.Copy

'''paste to the number of rows based on cell AD1
''    ActiveCell.Range("A1:Z5").Select
    ActiveCell.Range("A1:Z" & Range("ad1")).Select
    ActiveSheet.Paste
    Selection.Copy

'Clear that non-blank cell so it will go to the next non-blank cell
    Application.Goto Reference:="R1C29"
    Selection.End(xlDown).Select
    Application.CutCopyMode = False
    Selection.Clear
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Clear

Next
Go_To_A1:
    Application.Goto Reference:="R1C1"
Err_Handler:
    Application.Goto Reference:="R1C1"

End Sub
 
Upvote 0
bobaol said:
Hello remmuS24, you can do this with a macro, but the sheet needs to be modified a little. You're saying you have 6 sections, and the number of rows will vary depending on how many rows need to be entered. but that is okay, here is how i would handle it. assume your current sheet is your template. you have to design a template so there is a starting point. so, in this template, in cells aa21, aa37, aa72, enter =$b$17. in cells ab21, ab37, ab72, enter =$e$17. be sure to save your template. Cheers!
VBA Code: 
''' Press F8 to see what the code does line-by-line

Sub macro1_all()
    Application.Run "macro3"
    Application.Run "macro4"
End Sub

Sub Macro3()
'cop AA AB to AC AD as values
    Application.Goto Reference:="R1C27"
    Columns("AA:AB").Select
    Selection.Copy
    Application.Goto Reference:="R1C29"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


Sub Macro4()
    On Error GoTo Err_Handler

For i = 1 To 444
   
'''go to AC1, XLDown to first non-blank cell, copy the cell next to it, in AD, paste it to AD1
    Application.Goto Reference:="R1C29"
    Selection.End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy
    Application.Goto Reference:="R1C30"
    ActiveSheet.Paste
    Selection.Copy
   
'''go back to AC1, find first non-blank cell again
    Selection.Copy
    Application.Goto Reference:="R1C29"
    Selection.Copy
    Selection.End(xlDown).Select
    Selection.Copy
   
'''go to column A
    ActiveCell.Offset(0, -28).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy

''''select the number you entered, and insert these number of rows
    ActiveCell.Range("A1:AE" & Range("AD1")).Select
    Selection.EntireRow.Insert
    Selection.Copy
   
   
'''go back to AC1, find first non-blank cell again
    Selection.Copy
    Application.Goto Reference:="R1C29"
    Selection.End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(0, -28).Range("A1").Select
    Selection.Copy
'''select A to Z to copy your formula
    ActiveCell.Range("A1:Z1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Selection.Copy

'''paste to the number of rows based on cell AD1
''    ActiveCell.Range("A1:Z5").Select
    ActiveCell.Range("A1:Z" & Range("ad1")).Select
    ActiveSheet.Paste
    Selection.Copy

'Clear that non-blank cell so it will go to the next non-blank cell
    Application.Goto Reference:="R1C29"
    Selection.End(xlDown).Select
    Application.CutCopyMode = False
    Selection.Clear
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Clear

Next
Go_To_A1:
    Application.Goto Reference:="R1C1"
Err_Handler:
    Application.Goto Reference:="R1C1"

End Sub
Click to expand...
Thank you bobaol. I must've messed something up in the copy over. When I modified cells aa21, aa37, aa72 and ab21, ab37, ab72 per your recommendation I "ran" the form and saved the worksheet as a macro-enabled and then closed out. When I opened the spreadsheet back up and entered values into cells E17 and I17, nothing happened except referencing of those cell values in the AA and AB ranges you recommended.
 
Upvote 0
Corridor Design Sheets - 20240517.xlsm
ABCDEFGHIJKLMNOPQR
1
2
3
4
5
6
7Shear Walls - RoofTypical Unit Loads
8DL (psf)LL (psf)Snow (psf)Wind (psf)
9Lateral Design Loads: Roof (ASD)Lateral Design Loads: Roof (Unfactored)Roof252012.6-
10Seismic55,661lbsSeismic79,515lbsFloor4440N/A-
11Wind - North/South207,464lbsWind - North/South345,774lbsBalcony226015-
12Wind - East/West30,916lbsWind - East/West51,527lbsExterior Wall50N/AN/A-
13Ω =1Interior Wall10N/AN/A-
14Sds = 0.125
15"X"Value denotes shear wall lengths that have exceeded a 2:1 aspect ratioFraming MaterialDFShtg Thickness15/32"
16Note: All shear wall sheathing based on Struct-1
17Number of shear walls in the N/S Direction5Number of shear walls in E/W Direction6
18
19
20Shear Wall DesignRoof - Uniform Shear (ASD)Level Above - Uniform Shear (ASD)Total Uniform Shear (ASD)
21Wall TypeWall Segment Length (ft)Total Shear Wall Length (ft)Tributary Area (ft²)Total Area (ft²)Seismic (LB/FT)Wind (LB/FT)Seismic (LB/FT)Wind (LB/FT)Seismic (LB/FT)Wind (LB/FT)Shear Wall TypeIs Strong Wall Needed?
22GridInterior3.51224510661066397310663973#N/AYes 
23GridInterior28452501066290108129010812-SW3No 
24Grid221      No 
25Grid350      No 
26Grid      No 
27Total Area =1066
28Corridor GridInterior106247819562449249449249SW4No 
29Corridor GridInterior1063.7547819562437242437242SW4No 
30Grid      No 
31Grid      No 
32Grid      No 
33Total Area =9562
34Note 1: All shear walls that exceed an aspect ratio of 2:1 recevied a capacity adjustment. Wall segments that exceed 3.5:1 have been eliminated from usage
35Note 2: Where party walls are shear walls, and BOTH party walls are being utilized for shear, the linear footage of shear wall is determined based on both walls combined (i.e. 2 walls of 28ft of shear wall each = 56ft total length)
36Note 3: Where party walls are shear walls, and party wall shear wall lengths are equal on both sides of the air gap, only one wall is designed with the design being applied to both. Where walls are unequal in length, or have breaks, all segments are broken out into individual walls
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Roof - Shear Wall Design
Cell Formulas
RangeFormula
M9M9=IF('Design Criteria'!$E$39="Gable",'Design Criteria'!$C$301,'Design Criteria'!$C$296)
L9:L11L9='Design Loads Summary'!L12
M11M11='Design Criteria'!$C$296
C10:C12C10='Lateral Load Summary'!F23
F10:F12F10='Lateral Load Summary'!F8
K9K9='Design Loads Summary'!F31
K10K10='Design Loads Summary'!F48
K11K11='Design Loads Summary'!F57
K12K12='Design Loads Summary'!F78
K13K13='Design Loads Summary'!F90
B14B14=IF('Design Loads Summary'!F155="Use Product Design Criteria",'Design Loads Summary'!D155,'Design Loads Summary'!D135)
G22:G26G22=IF(D22>0,$F$27,"")
H22:H26,H28:H32H22=IF(E22>0,((F22/G22)*$C$10)/E22,"")
I22:I26I22=IF(E22>0,((F22/G22)*$C$11)/E22,"")
L22:L26,L28:L32L22=IF(E22>0,H22+J22,"")
M22:M26,M28:M32M22=IF(E22>0,I22+K22,"")
N22:N26N22=IF(AND(E22>0,D22>0), IF(AND(D131/D22<3.5,D131/D22<2),INDEX(SW_Type,MATCH(MAX(L22:M22),SW_Capacity_Reduced,-1)), INDEX(SW_Type,MATCH((MAX(L22:M22)/((2*D22)/D131)),SW_Capacity_Reduced,-1))),"")
O22:O26,O28:O32O22=IF(ISNA(N22),"Yes","No")
P22:P26P22=IF(AND(E22>0,D22>0),IF(D131/D22>3.5,"You've exceeded the max aspect ratio of 3.5:1",""),"")
F27,F33F27=SUM(F22:F26)
G28:G32G28=IF(D28>0,$F$33,"")
I28:I32I28=IF(E28>0,((F28/G28)*$C$12)/E28,"")
N28:N32N28=IF(AND(E28>0,D28>0), IF(AND(D136/D28<3.5,D136/D28<2),INDEX(SW_Type,MATCH(MAX(L28:M28),SW_Capacity_Reduced,-1)), INDEX(SW_Type,MATCH((MAX(L28:M28)/((2*D28)/D136)),SW_Capacity_Reduced,-1))),"")
P28:P32P28=IF(AND(E28>0,D28>0),IF(D136/D28>3.5,"You've exceeded the max aspect ratio of 3.5:1",""),"")
E29E29=63+9/12
Named Ranges
NameRefers ToCells
SW_Capacity_Reduced='Lateral and Uplift Capacity'!$C$31:$C$36N28:N32, N22:N26
SW_Type='Lateral and Uplift Capacity'!$B$11:$B$16N28:N32, N22:N26
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P22:Q32Cell Valuecontains "You've exceeded the max aspect ratio of 3.5:1"textNO
D22:D26,D28:D32,C131:D140,C156:C165Expression='Design Criteria'!$E$36/C22>2textNO
Cells with Data Validation
CellAllowCriteria
C22:C26List=$U$2:$U$3
C28:C32List=$U$2:$U$3
K15ListDF, HF, SPF
M15List3/8", 7/16", 15/32"
 
Upvote 0
after you re-open the file, enter you numbers in E17 and I17, then press Alt+f8, the run macro box should show up. click on "macro1_all", and click Run. Also, please press Alt+f11 to go into the macro editor and change For i = 1 To 444 to For i = 1 To 6 otherwise, if it is 444, it may run for a very long time. since you have only 6 sections, it only needs to repeat 6 times. If you don't know how to change the code, don't worry about it. it should still run, but will take longer time than necessary. my bad on that part. Cheers!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Insert new row with incremental formula
Replies
8
Views
583
Sheik
S
B
  • Question Question
VBA to insert rows based on cells column in another sheet (same workbook)
2 3
Replies
22
Views
652
Joe4
Joe4
M
  • Solved
Automate insertion of row data
2
Replies
12
Views
478
myall_blues
myall_blues
F
  • Solved
Auto number when a row is inserted
Replies
7
Views
517
FireBott
F
B
  • Question Question
Stop Excel rounding decimal numbers up to next integer
Replies
5
Views
642
rlv01
R

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

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
Back
Top