In this article, we’ll illustrate how to insert data into an Excel table using VBA code. With the help of Excel’s built-in properties and methods, we can facilitate the functionality of adding or overwriting data into a table easily. Let’s dive into the examples to get a clear understanding of the methods along with the VBA codes.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Examples to Insert Data into Table Using VBA in Excel
Let’s say, we have a sale list of a shop with details like the order date, product name, quantity, unit price, and total price.
How to Create a Table in Excel
To turn the dataset into an Excel table, do the following-
- Select the whole dataset.
- Go to the Insert tab from the Excel Ribbon.
- Click on the Table option.
- Finally, click the OK button in the Create Table window.
- We’ve successfully converted the dataset into an Excel table.
To insert data into an Excel table, we’ll use several VBA functions and properties in our code. The following section describes how to open and write code in the visual basic editor.
Write Code in Visual Basic Editor
Follow the steps to open the visual basic editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic for Applications window, click the Insert dropdown to select the New Module
Now that a new module is opened, write some code there and press F5 to run.
1. Insert Data at the Last Row of a Table Using VBA in Excel
In this example, we’ll use a VBA code to add a new row at the bottom of the table and then insert data into that. In our table, we have 4 rows of data. To add the 5th one, copy and paste the following code into the visual basic editor.
Sub InsertDataIntoTable()
Dim tableName As ListObject
Set tableName = ActiveSheet.ListObjects("Table1")
Dim addedRow As ListRow
Set addedRow = tableName.ListRows.Add()
With addedRow
.Range(1) = "1/1/2022"
.Range(2) = "Apple"
.Range(3) = 5
.Range(4) = 1.77
End With
End Sub
Press F5 to run the code.
We’ve inserted a new row of data at the bottom of the existing table.
Code Explanation:
- In our code, we used the ListObjects object of VBA Excel to grab the table with its name. For more information, each of the ListObject objects is a table on the worksheet. To get the table name, click on the table >> click on the Table Design tab in the Excel Ribbon.
- Then, we used the ListRows.Add method to add a new row in the table. This method takes two arguments: Position and AlwaysInsert.
By entering an integer number, we can specify the relative position of the newly added row in the table. In this example, we left blank the position argument, as a result, a new row has been added at the bottom of the table.
- Finally, we set data in each of the cells of the newly added row. In this case, the data were .Range(1) = “1/1/2022” as OrderDate, .Range(2) = “Apple” as Product, .Range(3) = 5 as Quantity, .Range(4) = 1.77 as Unit Price.
Read More: Excel Table Name: All You Need to Know
2. Run a VBA Code to Insert Data at a Specific Row of a Table in Excel
In this illustration, we’re going to add a row of data at a specific row relative to the table using the same piece of code that we used in the above example. In this case, we just need to specify the Position argument of the ListRows.Add method in our code. Let’s say we want to add sale data for Orange with details in row number 3 relative to the existing table. For this to happen, copy and paste the following code into the visual code editor.
Sub InsertDataIntoTable()
Dim tableName As ListObject
Set tableName = ActiveSheet.ListObjects("Table1")
Dim addedRow As ListRow
Set addedRow = tableName.ListRows.Add(3)
With addedRow
.Range(1) = "1/1/2022"
.Range(2) = "Orange"
.Range(3) = 3
.Range(4) = 2.14
End With
End Sub
Running the code by pressing F5 inserted the sale data for Orange in the 3rd row relative to the table.
Read More: Excel VBA Code for Each Row in a Table (Add, Overwrite, Delete, etc.)
Similar Readings
- Calculated Field Sum Divided by Count in Pivot Table
- How to Illustrate Relative Frequency Distribution in Excel
- [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
- How to Calculate Percent Frequency Distribution in Excel (2 Methods)
- Pivot Table Not Refreshing (5 Issues & Solutions)
3. Insert and Overwrite Data into a Table Using VBA in Excel
Here we’ll illustrate how to overwrite the existing data in a table instead of inserting a new row of data. To do that, we need to use the ListObject.ListRows property of Excel instead of the ListRows.Add property we used earlier. The ListObject.ListRows property also takes row number (Integer value) as its argument. Let’s say, we want to change the unit price of the Orange from 2.14 to 2.35 that we inserted in the previous example. Copy and paste the following code below in the visual basic editor.
Sub InsertDataIntoTable()
Dim tableName As ListObject
Set tableName = ActiveSheet.ListObjects("Table1")
Dim addedRow As ListRow
Set addedRow = tableName.ListRows(3)
With addedRow
.Range(1) = "1/1/2022"
.Range(2) = "Orange"
.Range(3) = 3
.Range(4) = 2.35
End With
End Sub
In our code, we set the argument for the ListObject.ListRows property as 3 where the data for Orange belongs to. Run the code by pressing F5 and see the difference.
The code has changed only the unit price of the data at row number 3 relative to the table.
Read More: How to Insert or Delete Rows and Columns from Excel Table
4. Run a VBA Code to Insert Data into an Excel Table Based on User Input
In this example, we’re going to insert the table name and sale details of a product as user input rather than hardcode it in the VBA code each time. Follow the instructions below to accomplish this.
- Copy and paste the following code into the visual code editor.
Sub InsertDataIntoTable()
Dim tableName As ListObject
Dim A, B, C, D, tName As String
tName = Application.InputBox(Prompt:="Name of the Table: ", Type:=2)
A = Application.InputBox(Prompt:="Order Date: ", Type:=2)
B = Application.InputBox(Prompt:="Product Name: ", Type:=2)
C = Application.InputBox(Prompt:="Quantity: ", Type:=2)
D = Application.InputBox(Prompt:="Unit Price: ", Type:=2)
Set tableName = ActiveSheet.ListObjects(tName)
Dim addedRow As ListRow
Set addedRow = tableName.ListRows.Add()
With addedRow
.Range(1) = A
.Range(2) = B
.Range(3) = C
.Range(4) = D
End With
End Sub
- Press F5 to run the code.
- Put the name of the table in the input box (Table1, in this example) and hit OK.
- Then put the values for Oder Date, Product Name, Quantity, and Unit Price in the input boxes that appeared sequentially. In this example, the values we used are 1/1/2022, Orange, 3, and 35.
- As a result, we’ve got a new row with the inserted data at the bottom of the table.
Read More: Excel Table Formatting Tips – Change the Look of the Table
Notes
When we added a new row with data using VBA code, the format, and formulas get carried to the new row automatically. In our example, the TotalPrice column outputs the product of the columns Quantity, and Unit Price. We just inserted only the Quantity and Unit Price values; the new row was inserted with the product of these two values in the Total Price column.
Conclusion
Now, we know how to insert data into an Excel table using VBA code. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- How to Use Slicers to Filter a Table in Excel 2013
- Navigating Excel Table: Selecting Parts of a Table and Moving a Table
- What is the Difference between a Table and a Range in Excel?
- How to Make Excel Tables Look Good (8 Effective Tips)
- Pivot Table Field Name Is Not Valid: 9 Causes and Corrections
- How to Group Dates in Pivot Table (7 Ways)
Good brother . help ful .