How to insert blank rows based on inserted number

[bfrantic]

Hi guys,
My objective is:
When I insert the number of people 10, the excel automatically insert 10 rows from rows number 5 to number 15? Could you please help me for writing the Macro???
Thanks

 

[VoG]

Try

Sub test()
Dim aRow As Variant
aRow = Application.InputBox("Enter number of rows", Type:=1)
If TypeName(aRow) = "Boolean" Then Exit Sub
Rows(5).Resize(aRow).Insert
End Sub

 

[bfrantic]

I really appreciate for your fast reply. It's really perfect

Just 2 questions:
1- I want to insert the number of desired rows in cell not in Message box; For example I want to ask in cell A1: "How many people" and I want to write the number in A2. Could u plz help me

2-I need when I insert the number, excell insert the rows automatically but now I have to open Macro and run it. How can I assign Macro to a cell?

Thanks a lot for your reply in advance

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aRow As Variant
If Target.Address(False, False) = "A2" Then
    aRow = Target.Value
    Rows(5).Resize(aRow).Insert
End If
End Sub

 

[bfrantic]

Thanks Peter,

It's around 6 hours that I am looking for this code and now in a few minutes you solved my problem. Just one more question

Now based on your perfect code, Excel has generated for example x rows, now I want excel automatically number this column from 1 to x. Is there any code for that?

I wish the best for you and I became one of your crazy fans...
Thanks!

 

[VoG]

Maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aRow As Variant, i As Long
If Target.Address(False, False) = "A2" Then
    aRow = Target.Value
    Rows(5).Resize(aRow).Insert
    For i = 5 To 5 + aRow - 1
        Range("A" & i).Value = i
    Next i
End If
End Sub

 

[bfrantic]

Yes, that's great

Thank you so much Peter!

 

[bfrantic]

Hi
Actually I faced to a problem; when I put the number of required rows in A2 (For example 3) and excel make 3 rows for me and number it from 1 to 3, but if I find that I have made a mistake and I need 4 rows, Excel make more 4 rows and again from 1 to 4, however totally I need 4 rows.
I want when I modify the number of rows, final rows would be based on modified one only not past No and current one. I really appreciate all your kind supports
Best Regards,

 

[bfrantic]

Could u plz help me?