VBA Add Column to Table in Excel |Tutorial |Examples

VBA Add Column to Table in Excel. We can add a single Column or multiple Columns and data to table. Default new Columns added at the end of the table. In this tutorial we have explained multiple examples with explanation. We also shown example output screenshots. We have specified three examples in the following tutorial. You can change table and sheet name as per your requirement. We also specified step by step instructions how to run VBA macro code at the end of the session.

Table of Formats:

Objective
Syntax to Add Column to Table using VBA in Excel
Example to Add New Column to Table on the Worksheet in Excel
Add Multiple Columns to Table in Excel using VBA
Add Column & Data to Table on the Worksheet in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax to Add Column to Table using VBA in Excel

Here is the syntax to add new Column to table on the worksheet using VBA in Excel.

expression.Add(Position, AlwaysInsert)

Where expression represents the ListColumns.
Position is an optional parameter. It represents the relative position of the new Column. Accepts the Integer value.
AlwaysInsert is an optional parameter. It represents the cells to be shifted to down or not, based on Boolean value. Accepts the Boolean value either True or False.

Note: If position is not specified, default adds new Column at the end of the table.

Example to Add New Column to Table on the Worksheet

Let us see the example to add new column to table on the worksheet. The sheet name defined as ‘Table‘. And we use table name as ‘MyDynamicTable‘. You can change these two as per your requirement. We Add method of the ListObject object.

'Add Column to Table in Excel VBA
Sub VBAF1_Add_Column_to_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyDynamicTable"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Add column to the table
    loTable.ListColumns.Add
    
End Sub

Output: Here is the following output screenshot of above example macro VBA code.

Add Multiple Columns to Table in Excel using VBA

Here is another example to add multiple columns to table. In this example we add five(5) columns to the table. You can specify the number of columns count in the for loop.

 'VBA Add Multiple Columns to Table
Sub VBAF1_Add_Multiple_Columns_to_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim iCnt As Integer
    
    'Define Variable
    sTableName = "MyDynamicTable"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    For iCnt = 1 To 5
        'Add multiple columns to the table
        loTable.ListColumns.Add
    Next
    
End Sub

Output: Let us see the following output screenshot of above example macro VBA code.

Add Column & Data to Table on the Worksheet in Excel

Let us see how to add new column and data to the table using VBA in Excel. In the below example we add new column and data of 5 columns.

 'VBA Add Column and Data to Table
Sub VBAF1_Add_Column_And_Data_to_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim lrColumn As ListColumn
    
    'Define Variable
    sTableName = "MyDynamicTable"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Add New Column to the table
    Set lrColumn = loTable.ListColumns.Add
    
    'Add Data to recently added column
    With lrColumn
        'Ignore Range(1), because its header not a data range.
        .Range(2) = 61
        .Range(3) = 62
        .Range(4) = 63
        .Range(5) = 64
        .Range(6) = 65
        .Range(7) = 66
        .Range(8) = 67
        .Range(9) = 68
        .Range(10) = 69
        .Range(11) = 70
    End With
       
End Sub

Output: Here is the following output screenshot of above example VBA macro code.