Insert new rows based on user input

[remmuS24]

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.

 

[bobaol]

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!

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