Insert Multiple Rows - Popup Box VBA

[bmkelly]

Hello,

I am looking for a macro that can insert 'x' amount of rows at ActiveCell that will give the user a pop box to enter however many rows they need to insert.

I am currently using this formula

Sub InsertRow()
ActiveCell.EntireRow.Resize(1473).Insert Shift:=xlDown
End Sub

The formula above works fine but would love to make it that much easier for users to have a pop up box appear so they can just insert in 'x' amount of rows instead of going into the Macro and changing the resize number (Example of above they would have to change the 1473 number every time they want to insert)

Thanks!

 

[Joe4]

Try this:

Sub InsertRow()
    
    Dim i As Integer
    
    On Error GoTo err_check
    i = InputBox("How many rows would you like to insert?")

    ActiveCell.EntireRow.Resize(i).Insert Shift:=xlDown
    
    Exit Sub
    
err_check:
    MsgBox "You must enter a valid number of rows to insert!", vbOKOnly, "ENTRY ERROR!"
    
End Sub

 

[Fluff]

How about

Sub bmkelly()
   Dim Rws As Variant
   
   Rws = InputBox("How many rows")
   If Rws = "" Then Exit Sub
   ActiveCell.EntireRow.Resize(Rws).Insert
End Sub