Insert Rows with Msg Box

[RandyD123]

I need to have a msg box pop up on my macro that asks "How Many Rows Would You Like To Insert?" Right now I only have this much:

Sub InsertBlankRows()
Application.ScreenUpdating = False
numRows = InputBox("How many Rows")
'Inserting a Row at at Row 5
Range("A5").EntireRow.Insert
Application.ScreenUpdating = True
End Sub

Right now it will insert only 1 row after row 5. I need it to ask me how many rows I want to insert after row 5. Any help would be appreciated. Thank you in advance

 

[Scott T]

Try

Sub InsertBlankRows()
Application.ScreenUpdating = False
numRows = InputBox("How many Rows")
If numRows <= 0 Then Exit Sub
'Inserting a Row at at Row 5
Rows(6 & ":" & 6 + numRows - 1).Insert 'inserts row after row 5
Application.ScreenUpdating = True
End Sub

 

[dmt32]

Hi,
try this update to your code:

Sub InsertBlankRows()
    Dim numRows As Variant
    
    Do
        numRows = InputBox("How many Rows?", "Insert Rows", 1)
'cancel pressed
        If StrPtr(numRows) = 0 Then Exit Sub
    Loop Until Val(numRows) > 0
    Application.ScreenUpdating = False
        Range("A5").EntireRow.Resize(Val(numRows)).Insert
    Application.ScreenUpdating = True
End Sub

Dave

 

[RandyD123]

Works Perfectly. Thank You

Try

Sub InsertBlankRows()
Application.ScreenUpdating = False
numRows = InputBox("How many Rows")
If numRows <= 0 Then Exit Sub
'Inserting a Row at at Row 5
Rows(6 & ":" & 6 + numRows - 1).Insert 'inserts row after row 5
Application.ScreenUpdating = True
End Sub

 

[RandyD123]

Maybe I spoke too soon!!! It seems that if I don't put a number of rows in the msg box and just cancel it, I get a Run-time error '13': There is no option to debug, the only option is to "end" and it does with no further errors.

 

[Scott T]

This should work If you want you can change the if so it has a message box like this

If numrows <= 0 Or Len(numrows) = 0 Then
    MsgBox "cancled or invalid number entered"
    Exit Sub
End If

Sub InsertBlankRows()
Application.ScreenUpdating = False
numrows = InputBox("How many Rows")
If numrows <= 0 Or Len(numrows) = 0 Then Exit Sub
'Inserting a Row at at Row 5
Rows(6 & ":" & 6 + numrows - 1).Insert 'inserts row after row 5
Application.ScreenUpdating = True
End Sub

 

[dmt32]

Maybe I spoke too soon!!! It seems that if I don't put a number of rows in the msg box and just cancel it, I get a Run-time error '13': There is no option to debug, the only option is to "end" and it does with no further errors.

did you try my suggestion?

Dave

 

[RandyD123]

Yes Dave and it works. Thank You.

 

[RandyD123]

Thank You once again.

 

[RandyD123]

Yes, this works perfectly. Thanks again for all the help. Both you and Dave provided great 100% working examples.

This should work If you want you can change the if so it has a message box like this

If numrows <= 0 Or Len(numrows) = 0 Then
    MsgBox "cancled or invalid number entered"
    Exit Sub
End If

Sub InsertBlankRows()
Application.ScreenUpdating = False
numrows = InputBox("How many Rows")
If numrows <= 0 Or Len(numrows) = 0 Then Exit Sub
'Inserting a Row at at Row 5
Rows(6 & ":" & 6 + numrows - 1).Insert 'inserts row after row 5
Application.ScreenUpdating = True
End Sub