Add row to Excel table without hard-coding table location-CodePudding

I have an Excel table (call it "MyTable"), and I want to create a macro to insert a blank new row as the first row. Since the table can be moved within the worksheet, I don't want the macro to use any hard-coded row or column addresses. Thanks in advance.

CodePudding user response：

Here are your options, just change the strTableName variable to be what it should be, i.e. if it's a true table, it's the table name and if it's a named range, it's the name you've given it.

True Table

Public Sub InsertRowToTable()
    Dim strTableName As String, objSheet As Worksheet, objListObject As ListObject
    
    strTableName = "MyTable"
    
    For Each objSheet In ThisWorkbook.Worksheets
        On Error Resume Next
        Set objListObject = objSheet.ListObjects(strTableName)
        On Error GoTo 0
        
        If Not objListObject Is Nothing Then Exit For
    Next
    
    objListObject.DataBodyRange.Rows(1).Insert xlShiftDown
End Sub

Named Range

This assume you have a header row, therefore, it inserts from row 2.

Public Sub InsertRowToNamedRange()
    Dim strTableName As String, objSheet As Worksheet, objName As Name
    
    strTableName = "MyTable"
    
    Set objName = ThisWorkbook.Names(strTableName)
        
    objName.RefersToRange.Rows(2).Insert xlShiftDown
End Sub

Named ranges are a little more painful when it comes to formats so best to stick to tables if you can, at least, for this scenario.