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"

70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf from our Excel training page
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf from our Excel training page
Learn how to fix these errors: #DIV/0!, #N/A!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!

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

 

Posted by Chester Tugwell