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.
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.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel VBA Tables and ListObjects
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog