excelvbaexcel-tableslistobject

VBA Insert Row to bottom of specific table and shift all cells down


I have a spreadsheet with multiple tabs and multiple tables on each tab. I want to add a button that adds a row to the bottom of a table and shifts the worksheet down so that the table doesn't run into the tables below. I have used a generic code found somewhere else on stack to add a row and then assigned it to a button, and it worked beautifully, but want to include a function to add a row to the whole sheet below the table. What do I add to the code to shift the sheet down? Thanks in advance!

Private Sub InsertRowsInTable(ByVal targetTableName As String)
    
    ' Ask user how many rows to ask
    Dim rowsToAdd As Variant
    rowsToAdd = InputBox("How many rows would you like to add?", "Insert Rows", 1)
    
    ' If user didn't input anything, default to 1
    If rowsToAdd = vbNullString Then rowsToAdd = 1
    
    Dim targetTable As ListObject
    Set targetTable = Range(targetTableName).ListObject
    
    ' Resize the table to add rows
    targetTable.Resize targetTable.Range.Resize(targetTable.Range.Rows.Count + rowsToAdd)
        
End Sub```

Solution

  • Thanks everyone for your responses!

    I solved it with a combination of Elio Fernandes and Vincent G's suggestion.

    I added For rowinsert = 0 To rowsToAdd - 1 when resizing my table. So what I ended up with was

    Private Sub InsertRowsInTable(ByVal targetTableName As String)
       
        ' Ask user how many rows to ask
        Dim rowsToAdd As Variant
        rowsToAdd = InputBox("How many rows would you like to add?", "Insert Rows", 1)
       
        ' If user didn't input anything, default to 1
        If rowsToAdd = vbNullString Then rowsToAdd = 1
       
        Dim targetTable As ListObject
        Set targetTable = Range(targetTableName).ListObject
       
        Dim rowinsert As Integer
       
        ' Resize the table to add rows
        For rowinsert = 0 To rowsToAdd - 1
            targetTable.Range.EntireRow(targetTable.Range.Rows.Count + 1).Insert
        Next
        targetTable.Resize targetTable.Range.Resize(targetTable.Range.Rows.Count + rowsToAdd) 
    End Sub
    

    I could then assign buttons using

    Public Sub InsertRowsInTable1()
        InsertRowsInTable "Table1"
    End Sub
    

    This let me add rows to my table and then shift everything down so the tables didnt run into each other.

    Thanks everyone again for your responses!