excelvbaexcel-tableslistobject

Add row only on the table, not in the whole sheet


I want to add a row only on the specified table and not on the whole sheet.

I want flexible code so I can use it in different templates; meaning:

Sub AddRowToTable()

Dim tbl As ListObject 'Needed to reference a table '
Dim selectedCell As Range
   
On Error Resume Next
                    'Temporarily ignore the error that might occur during exec.'
                    'e.g., user clicks Cancel / ESC'
                    
Set selectedCell = Application.InputBox("Select a cell in the table below:", Type:=8)
    'Prompt the user to select a cell'
    
On Error GoTo 0

Set tbl = selectedCell.Worksheet.ListObjects(1) 'Assumes that there is only one table in the active sheet'

If Not tbl Is Nothing And Not selectedCell Is Nothing Then 'Check if the selection is within the table'
    
    If Not Intersect(selectedCell, tbl.DataBodyRange) Is Nothing Then
    
        selectedCell.Offset(1, 0).EntireRow.Insert
             'Insert a row below the selected cell within the table'
    Else
        MsgBox "Please select a cell within the table below."
    End If
Else
    MsgBox "No table found in the active sheet. Contact template owner."
End If
End Sub

Solution

  • Please try

    Application.Intersect(selectedCell.EntireRow, tbl.Range).Insert
    

    OR

    tbl.ListRows.Add selectedCell.Row - tbl.DataBodyRange.Row + 1
    

    Microsoft documentation:

    ListRows.Add method (Excel)

    ListObject.DataBodyRange property (Excel)