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​

 

[My Aswer Is This]

Show us all the code you have in your sheet.

hi
it all got highlighted

 

[sbrown64]

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​

 

[sbrown64]

if i go to the last line of the table and try it it doesnt work, so i tabbed until i created a few blank lines and then went back to the first line to enter a number and then it did?

 

[My Aswer Is This]

This code works for me.
Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/14/2019  5:43:33 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And IsNumeric(Target.Value) And Target.Value > 1 Then Rows(Target.Row).Offset(1).Resize(Target.Value - 1).Insert Shift:=xlDown
Application.EnableEvents = True
End Sub

 

[sbrown64]

hi

exactly the same result, does not work on the last line, but if i insert one and then go back to the first line, it does. I am happy with this, as long as i remember to insert a line i am ok.

Thank you.

 

[My Aswer Is This]

What does this mean:

does not work on the last line

The last row of the Worksheet ??

Or do you mean the last row of a table

If so what is the name of the Table

And your wanting to insert rows into the Table ??

So if your on the last row of the Table and enter 3 in column B you want to add 2 more rows to the Table is that what you want?
​

 

[sbrown64]

Hi
Yes as stated earlier, it is a table, so the last entry is on line15. I then tab down to create a new line, i put the date in column A and then tab to column B, i then type in any number and no extra lines are added, so i continue filling out columns C-J. I then press tab to create a new line, if i then go back up to the line 16 and re-type the number in column B, it works.

 

[My Aswer Is This]

Any time your referring to a Table we need to know the name of the Table.

This script assumes your Table is named Table1

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/14/2019  6:57:33 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim i As Long
Application.EnableEvents = False
If Target.Column = 2 And IsNumeric(Target.Value) And Target.Value > 1 Then
With ActiveSheet.ListObjects("Table1")
    For i = 1 To Target.Value - 1
        .ListRows.Add (Target.Row)
    Next
End With
Application.EnableEvents = True
MsgBox "Rows added to Table"
End If
End Sub

 

[sbrown64]

Hi

I tried your code above and nothing happened, I changed the code to Table2

 

[My Aswer Is This]

We are up to post 39 and it seems like I'm not able to help you.

You must enter a number in column B of the Table named Table1

You cannot be in row 40 if the last row of the table is 39

And when we are referring to a Table we refer to the column number

Like column 2 of Table1 but I assume your Table starts in sheet column(1)