Dim i As Long
Dim n As Long

On Error GoTo CancelInput
n = InputBox("No. of Rows")
CancelInput:

For i = 1 To n
Selection.Insert Shift:=xlDown
Next i

P.S. Throw out "Dim ... As Integer" Get in the habit of always using Long.
You won't regret it.

"Rob" wrote:

Hello, I have the below code that prompts for the qty of rows to be inserted
but when the Cancel button is selected instead of providing a number and
clicking OK I get an error. I was hoping that putting the IF statement in
there would fix it but it did not. How can I fix this?

Thanks in Advance.


Sub AutoInsertRows()

'Shortcut Keystroke CTRL+Shift+A

Dim r As Integer

r = InputBox("How Many Rows?", "Auto-Insert Rows", 1)
i = 0

Do While i < r

If Not r = vbCancel Then

Selection.Insert Shift:=xlDown

i = i + 1

Else

Exit Sub

End If

Loop

End Sub