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.
A table is a special type of data set in Excel where the first row contains the headers.
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.]
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"
It’ll change the name of the table to MyTable.
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")
It refers to the table “MyTable” in Excel with the name Table1.
Read more: How to Use Excel Table Reference
3. Entering Values in the Table with Excel VBA
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.
4. Inserting Rows and Columns into a Table with Excel VBA
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
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
It’ll add a new column to the right of the table MyTable.
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.
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)
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
- VBA to Refresh Pivot Table in Excel (5 Examples)
- How to Refresh All Pivot Tables with VBA (4 Ways)
- Edit a Pivot Table in Excel (5 Methods)
- How to Update Pivot Table Range (5 Suitable Methods)
6. Looping through the Rows or Columns of a Table Using 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
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 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
It’ll look for the value “Mother” in the 1st column of the table MyTable, and return the value if it finds one.
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
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Using these methods, you can use a table with VBA. Do you have any questions? Feel free to ask us.
“It refers to the table “MyTable” in Excel with the name Table1.”
there is a mistake here , the name is MyTable
Dear ASKA,
Thank you for bringing the mistake to our attention. We apologize for the error in my previous response. You are correct, the name of the table in Excel should be “MyTable,” not “Table1”.
I apologize for any confusion caused by the incorrect information. If you have any further questions or need assistance with any other topics, please feel free to ask.
Best regards,
Al Ikram Amit
Team ExcelDemy