Skip to content

Excel VBA – How to Add Rows and Columns to Excel Table with VBA Macro

    Add a Column to a Table

    To add a column to an Excel table use ListColumns.Add and specify the position of the new column.

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Sales_Table")
    'add a new column as the 5th column in the table
    tbl.ListColumns.Add(5).Name = "TAX"
    'add a new column at the end of the table
    tbl.ListColumns.Add.Name = "STATUS"

    Add a Row to a Table

    To add a row to an Excel table use ListRows.Add and specify the position of the new row.

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Sales_Table")
    ‘add a row at the end of the table
    tbl.ListRows.Add
    ‘add a row as the fifth row of the table (counts the headers as a row)
    tbl.ListRows.Add 5

    Add Row and Enter Data

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl =ws.ListObjects("Sales_Table")
    Dim newrow As ListRow
    Set newrow = tbl.ListRows.Add
    With newrow
        .Range(1) = 83473
        .Range(2) = "HJU -64448"
        .Range(3) = 5
    End With

    Add/Overwrite Data in a Specific Record

    Dim ws AsWorksheet
    Set ws =ActiveSheet
    Dim tbl AsListObject
    Set tbl =ws.ListObjects("Sales_Table")
    Withtbl.ListRows(3)
       .Range(3)= 8
       .Range(6)= "CASH"
    End With

    Delete Row or Column

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Sales_Table")
    tbl.ListColumns(2).Delete
    tbl.ListRows(2).Delete