Excel VBA: Insert Data into Table (4 Examples)

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.

Table of Contents hide

Download Practice Workbook

4 Examples to Insert Data into Table Using VBA in Excel

1. Insert Data at the Last Row of a Table Using VBA in Excel

2. Run a VBA Code to Insert Data at a Specific Row of a Table in Excel

3. Insert and Overwrite Data into a Table Using VBA in Excel

4. Run a VBA Code to Insert Data into an Excel Table Based on User Input

Notes

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Insert Data into Table.xlsm

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.

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.

Similar Readings

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.

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.

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.