How to Use an Excel Table with VBA (9 Possible Ways)

The Table is one of the most important and widely used objects that we use while working with VBA in Excel. In this article, I’ll show you how you can use an Excel Table with VBA.


Download Practice Workbook

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


9 Ways to Use an Excel Table with VBA

A table is a special type of data set in Excel where the first row contains the headers.

Table to Use in Excel VBA

In VBA, it falls under the category ListObject. Here are the ways by which you can use an Excel Table in VBA.


1. Creating a Table with VBA in Excel

First of all, we’ll learn how to create a Table with VBA in Excel.

To create a Table with VBA in Excel, first, you have to declare the Table as a ListObject.

Then you have to insert the Table in your desired 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 in my active worksheet where I want the table. You use anything you like.]

VBA Code to Create an Excel Table with VBA

Run this code. It’ll create a Table in the range B4:D13 of your active worksheet.

Now if you wish, you can change the name of the table to anything you like.

Table1.Name = "MyTable"

Naming an Excel Table with VBA

It’ll change the name of the table to MyTable.

Refering to an Excel Table with VBA

Read more: Create Table in Excel Using Shortcut


2. Referring to an Excel Table with VBA

We’ve learned to create a Table with VBA in Excel.

Now to refer to a Table already created in Excel, you have to first declare the Table as a ListObject.

Then refer to the Table with the name available in Excel.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

VBA Code to Refer to an Excel Table with VBA

It refers to the table “MyTable” in Excel with the name Table1.

Refering to an Excel Table with VBA

Read more: How to Use Excel Table Reference


3. Entering Values in the Table with VBA in Excel

To enter values inside a Table, first, you have to refer to the Table.

Then you have to 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’ll enter the string “A Tale of Two Cities” in the 2nd row and 1st column of the table MyTable of the active worksheet.

Entering a Value to an Excel Table with VBA


4. Inserting Rows and Columns into a Table with VBA in Excel

To insert a row or column to a Table with VBA, first refer to the Table.

Then to add a row insert the ListRows.Add property.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListRows.Add

VBA Code to Use an Excel Table with VBA

It adds a new row to the end of the table MyTable.

Or to add a column, insert the ListColumns.Add property.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.ListColumns.Add

VBA Code to Add a Column to an Excel Table with VBA

It’ll add a new column to the right of the table MyTable.

Adding a Column to an Excel Table with VBA

Again, 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)

It adds a new row as the 4th row of the table MyTable.

Adding a New Row to an Excel Table with VBA

Same for adding a new column.

Read more: How to Insert or Delete Rows and Columns from Excel Table


5. Reading Data from a Table with VBA

To read any 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)

VBA Code to Read Data from an Excel Table with VBA

Run the code, it’ll display the value from the 3rd row and 1st column of the table MyTable. It’s The Forty Rules of Love.


Similar Readings


6. Looping through the Rows or Columns of a Table with VBA

To loop through each row of a table with 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

VBA Code Iterate Through a Row in an Excel Table with VBA

It’ll iterate through each row of table Table1 and select it.

Similarly, to iterate through each column of the table, use the ListColumns.Count property.


7. Looking for a Specific Value in a Table

To look for a specific value in a row or column of a table with VBA, use DataBodyRange.Rows.Find or the DataBodyRange.Columns.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

VBA Code to Look for a Value in an Excel Table with VBA

It’ll look for the value “Mother” in the 1st column of the table MyTable, and return the value if it finds one.

Looking for a Value in an Excel Table with VBA


8. Resizing a Table with VBA in Excel

In order to resize a table to a new shape, first set the Range to which the Table will be resized.

Then 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

VBA Code to Resize an Excel Table with VBA

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


9. Deleting a Table with VBA in Excel

To delete a table with VBA, use the Delete property of VBA.

Dim Table1 As ListObject

Set Table1 = ActiveSheet.ListObjects("MyTable")

Table1.Delete

It’ll delete the table MyTable from the active worksheet.


Things to Remember

A Table is a ListObject in VBA. But if you want to access it as a simple Range object, use the Table.Range property of VBA. It’ll return the table as a Range object, and you’ll be able to access all the properties of a Range object of VBA.


Conclusion

Using these methods, you can use a table with VBA. Do you have any questions? Feel free to ask us.


Further Readings

Tags:

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo