How to Use VBA in an Excel Table -9 Methods

This dataset showcases a table with headers.

Method 1 – Creating a Table with VBA in Excel

Declare the Table as a ListObject.
Insert the Table in a chosen range.

Dim Table1 as ListObject

Set Table1 = ActiveSheet.ListObjects.Add(xlSrcRange, Range("B4:D13"), , xlYes)

[Here Table1 is the name of the Table in VBA and B4:D13 is the range ]

Run this code. It will create a Table in B4:D13 of your active worksheet.

You can change the name of the table.

Table1.Name = "MyTable"

MyTable, here.

Read more: Create Table in Excel Using Shortcut

Method 2 – Referring to an Excel Table with VBA

Declare the Table as a ListObject.
Refer to the Table with a name.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

“MyTable” is referred to as Table1.

Read more: How to Use Excel Table Reference

Method 3 – Entering Values in the Table with Excel VBA

Refer to the Table.
Enter values in a cell by using the Range.Cells property of VBA.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.Range.Cells(2, 1) = "A Tale of Two Cities"

Run this code. It will enter the string “A Tale of Two Cities” in the 2nd row and in the 1st column of MyTable.

Method 4 – Inserting Rows and Columns into a Table with Excel VBA

Refer to the Table.
Add a row and insert ListRows.Add.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListRows.Add

A new row is added at the end of MyTable.

To add a column, insert ListColumns.Add.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListColumns.Add

It will add a new column to MyTable.

To add a row or column to a specific position of a Table, specify the position within the ListRows.Add or the ListColumns.Add property.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListRows.Add (4)

A new row (4th) was added to MyTable.

Follow the same procedure to add a new column.

Method 5 – Reading Data from a Table with VBA

Use the Range.Cells property of VBA.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

MsgBox Table1.Range.Cells(3, 1)

Run the code. It will display the values from the 3rd row and the 1st column in MyTable.

Similar Readings

Method 6 – Looping through the Rows or Columns of a Table Using VBA

Use the ListRows.Count property.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")


For i = 1 To Table1.ListRows.Count

    Table1.ListRows(i).Range.Select

Next i

It will iterate through each row of Table1 and select it.

To iterate through each column, use the ListColumns.Count property.

7. Looking for a Specific Value in a Table

Use the DataBodyRange.Rows.Find property of VBA.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Set Value = Table1.DataBodyRange.Columns(1).Find("Mother", LookAt:=xlWhole)

MsgBox Value

It will look for the value “Mother” in the 1st column of MyTable, and return the value if it is found.

Method 8. Resizing a Table with VBA in Excel

Set the Range to which the Table will be resized.
Resize the Table to that Range.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

 
Dim Rng As Range

Set Rng = Range("B4:C8")

 
Table1.Resize Rng

It sets the Range as B4:C8, then resizes MyTable to the new Range.

Method 9 – Deleting a Table with VBA in Excel

Use the Delete property of VBA.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.Delete

It will delete MyTable from the active worksheet.

Download Practice Workbook

Download the practice workbook to exercise.

VBA Table.xlsm