Forum Discussion
Inserting Rows Automatically
I have tweaked the code. Please replace the existing code with the following code and let me know if this works as desired now.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub On Error GoTo Skip: If Not Intersect(Target, Range("B:B")) Is Nothing Then Application.EnableEvents = False If Target.Offset(0, -1) <> "" Then If Target <> "" Then Rows(Target.Row + 1).Insert Target.Offset(1, -1) = Target.Offset(0, -1) Else If Application.CountIf(Columns(1), Target.Offset(0, -1).Value) > 1 Then Rows(Target.Row).Delete End If End If End If End If Skip: Application.EnableEvents = True End SubIf that takes care of your original question, please mark your question as Solved by accepting the Answer.
Hi
I can understand your question in 2 different ways:
1- when you add a number ANYWHERE in column B you want a new row inserted at the bottom of the Table.
2- When you add a number in the LAST empty cell in column B in your table you need a new row to be added to the table.
For option 1, you can add a spin button that Inserts or delete rows. If you are familiar with VBA and inserting controls then insert a spin button control and write this code for the button. It will insert/Delete a row with each click
Private Sub SpinButton1_SpinDown()
Dim LastRow As Long
LastRow = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
Rows(LastRow - 1).EntireRow.Delete
End Sub
Private Sub SpinButton1_SpinUp()
Dim LastRow As Long
LastRow = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
Rows(LastRow).EntireRow.Insert
End Sub
For Option 2 you also need to write a code for inserting a row but this time, attach it to the Change Event of the first empty cell in column B...
In either case I would have a preference to do it manually using the regular functionality of Excel.
Good Luck
