insert rows automatically

[sbrown64]

HiI have created a table & in column B I enter a number (i.e 3) is there a way to automatically add 2 rows below the line, so if the number was 5, it would add 4 rows below?RegardsStephen​

 

[Akuini]

Hi, sbrown64, I read your file.
You need to explain that your data is in actual table & when you say you insert a number in column B you mean you always do it in last row of the table, right?
So basically what you want is adding some rows at the bottom of the table?

 

[Fluff]

@sbrown64
Could you please answer my question from post#8
Cheers

 

[sbrown64]

Hi Fluff, no error message.
Akuini if you see the file, what I am doing is entering a football result, so I have a drop down on the end column so I can choose more names if needed. So if I enter 3 in column B then I want 2 extra lines, if I enter 4, I would want 3.

 

[Akuini]

Hi Fluff, no error message.
Akuini if you see the file, what I am doing is entering a football result, so I have a drop down on the end column so I can choose more names if needed. So if I enter 3 in column B then I want 2 extra lines, if I enter 4, I would want 3.

In that case the code from My Aswer Is This works.
I type 3 in B5 & it insert 2 blank rows below it.

Is it possible that the Application.EnableEvents is turned off?
Try running this code:

Sub toEvent()
Application.EnableEvents = True
End Sub

then try typing a number in B5, then exit the cell.

 

[Fluff]

Another option is

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value < 2 Then Exit Sub
   On Error GoTo Xit
   Application.EnableEvents = False
   If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert
Xit:
   Application.EnableEvents = True
End Sub

This will prevent an error if you enter 1 into column B.

 

[sbrown64]

Hi Fluff

I entered your code and entered 3 in column B line16, i then tabbed through to line19 and entered 4 and nothing happened?

 

[Fluff]

Have you tried running the code Akuini supplied in post#14?

 

[sbrown64]

hi

I have this code on the sheet1 page.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Sub Improve()
Application.EnableEvents = True
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Value < 2 Then Exit Sub
On Error GoTo Xit
Application.EnableEvents = False
If Target.Column = 2 And IsNumeric(Target.Value) Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert
Xit:
Application.EnableEvents = True
End Sub

Regards
Stephen
[/FONT]

 

[Fluff]

Did you run the code that that Akuini supplied in post#14?

 

[sbrown64]

yes it is included see post #18 at the top