Insert given numbers of rows above selected row

[gopal baheti]

Need to Insert rows given in Cell A1 above active row.
If i select row no 1000 and cell A1 Contains 5 then insert 5 rows above row no. 1000.
Regards

 

[My Aswer Is This]

Try this:

Sub Insert_Rows()
Application.ScreenUpdating = False
Dim ans As Long
Dim nr As Long
nr = Selection.Row
ans = Range("A1").Value
Rows(nr).Resize(ans).Insert xlShiftUp
Application.ScreenUpdating = True
End Sub

 

[gopal baheti]

Thanks. Worked perfectly.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks. Worked perfectly.

 

[My Aswer Is This]

This modified version assures a numeric value is in Range ("A1")
And that the value is greater then (0)

Sub Insert_Rows()
Application.ScreenUpdating = False
Dim ans As Long
Dim nr As Long
nr = Selection.Row
    If IsNumeric(Range("A1")) = True And Range("A1") > 0 Then
        ans = Range("A1").Value
        Rows(nr).Resize(ans).Insert xlShiftUp
        Else
        MsgBox "Inproper value in Range(A1)"
    End If
Application.ScreenUpdating = True
End Sub