Adding rows to a table VBA

[jlabrecque]

So I have this code that works to add rows to a table.

Sub insert_rows()

Dim x As Integer
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)

Range("A" & Rows.Count).End(xlUp).Select
Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown

End Sub


If you are using blue and white row 1 would be blue and row 2 white and row 3 blue again etc.

When the above code is used. The table loses its format as discussed above

I think the problem is occurring with how I am selecting the range.

Any advice is appreciated

 

[Norie]

Are the rows definitely being added to the table?

 

[jlabrecque]

yep, formulas are transferring down

 

[Norie]

Does this work any better?

Sub insert_rows()
Dim tbl As ListObject
Dim rng As Range
Dim x As Long

    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
    
    Set tbl = ActiveSheet.ListObjects("Table1")

    Set rng = tbl.ListRows(tbl.ListRows.Count).Range

    rng.Resize(x).Insert Shift:=xlDown

End Sub

 

[jlabrecque]

Yep. Thanks for your help