Insert Multiple Rows - Popup Box VBA

bmkelly

New Member
Joined
Mar 26, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub bmkelly()
   Dim Rws As Variant
   
   Rws = InputBox("How many rows")
   If Rws = "" Then Exit Sub
   ActiveCell.EntireRow.Resize(Rws).Insert
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,814
Messages
5,574,486
Members
412,597
Latest member
Timtec
Top