How to Use Table Reference with Excel VBA (20 Examples)

In Excel workbooks with large datasets, it can be convenient to refer to a table by its name instead of its whole range. Using VBA in Excel is the fastest, most reliable, and most efficient method of performing any operation. If you are looking for special tricks to know “how to reference a table with Excel VBA”, you’ve come to the right place. There are numerous ways of referencing a table in Excel. This article will discuss the details of these methods. Let’s follow the complete guide to learn all of this.

Reference a Table with ListObject Property

While writing VBA code, ListObjects are used to refer to Excel tables. Excel Tables can be created and deleted with the help of VBA LISTOBJECTS. The use of Excel Tables, however, can be challenging for beginners, and even to some extent for intermediate users. Due to the fact that this article discusses referencing Excel tables. It is important to have a good understanding of Tables in Excel when coding VBA.

For instance, to select the entire table in the Excel worksheet you have to write the following VBA code.

ActiveSheet.ListObjects("Table_ref_1").Range.Select

This line of code will select the table (“Table_ref_1” is the table name in our dataset).

How to Use Table Reference with Excel VBA: 20 Suitable Examples

The following section will use twenty effective and tricky examples to reference a table in Excel. To do the task, you need to use the VBA code. This is Microsoft’s event-driven programming language called Visual Basic for Applications (VBA). To use this feature you first need to have the Developer tab showing on your ribbon. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

1. Select an Entire Table

Here, we will demonstrate how to select an entire table.  First of all, in order 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. For demonstration purposes, we will use the following dataset.

To do this, you have to follow the following process.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Select_whole_Table()
ActiveSheet.ListObjects("Table_ref_1").Range.Select
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will select the entire table as shown below.

2. Copy an Entire Table

In this method, we will show you how to copy the entire table. Following is a dataset that we will use to demonstrate our point to reference a table with Excel VBA.

Let’s walk through the following steps to copy the Entire table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub copy_whole_Table()
ActiveSheet.ListObjects("Table_ref_18").Range.Copy
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will copy the Entire table as shown below.

3. Referencing a Column of a Table

Here, we will demonstrate how to reference a table of a column. Following is a dataset that we will use to demonstrate our point to reference a table with Excel VBA.

Let’s walk through the following steps to refer to a column of a table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub ReferenceEntireColumn()
ActiveSheet.ListObjects("Table_ref").ListColumns("Seller").Range.Select
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will select the entire column of the table as shown below.

4. Get a Value from a Cell from a Table

Here, we will demonstrate how to get a value from a cell from a table.  For the purpose of demonstrating our point, we have provided a dataset below.

Let’s walk through the following steps to get a value from a cell from a table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub get_Value()
Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("Table_ref_2")
Set Value = Table1.DataBodyRange.Columns(1).Find("Sam", LookAt:=xlWhole)
MsgBox Value
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will get a value from a cell from a table as shown below.

5. Entering a Row

Now, we will illustrate how to enter a new row in an existing table. For demonstration purposes, we will use the following dataset.

Let’s walk through the following steps to enter a new row in an existing table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Entering_row()
Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("Table_ref_3")
Table1.ListRows.Add
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will enter a new row in an existing table as shown below.

Read More:  Excel VBA Code for Each Row in a Table

6. Entering a Column

A new column will be added to an existing table in the following demonstration. For demonstration purposes, we will use the following dataset.

Let’s walk through the following steps to enter a new column in an existing table.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Entering_Column()
Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("Table_ref_4")
Table1.ListColumns.Add
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will enter a new column in an existing table as shown below.

7. Filtering a Table

The following example demonstrates how to filter a table in Excel.  For the purpose of demonstrating our point, we have provided a dataset below.

Let’s walk through the following steps to filter a table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Select_AutoFiltered_VisibleRows_NewSheet()
 ActiveSheet.ListObjects("Table_25").Range.AutoFilter _
    Field:=2, Criteria1:="=Texas"
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will filter the table Based on “Texas” text as shown below.

8. Deleting Rows and Columns from a Table

We will demonstrate how to delete rows and columns from a table here. Using the following dataset, we will demonstrate our point.

Let’s walk through the following steps to delete rows and columns from a table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub delete_row_column()
ActiveSheet.ListObjects("Table_ref_9").ListRows(2).Delete
ActiveSheet.ListObjects("Table_ref_9").ListColumns(2).Delete
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will delete rows and columns from a table as shown below.

9. Selecting Data from a Table Without Column Header

Here, we will demonstrate how to select data from a table without a column header. The dataset looks like this:

Let’s walk through the following steps to select data from a table without a column header in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub ReferenceEntireColumnWithoutHeader()
ActiveSheet.ListObjects("Table_ref_6").ListColumns("Seller").DataBodyRange.Select
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will select data from a table without a column header as shown below.

10. Selecting a Column Header

In this section, we will show you how to select a column header. In order to demonstrate our point, we have provided the dataset below.

Let’s walk through the following steps to select a column header in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Selecting_Column_Header()
ActiveSheet.ListObjects("Table_ref_7").HeaderRowRange(4).Select
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will select a column header as shown below.

Read More:  How to Create a Table with Headers Using Excel VBA

11. Select Entire Row

Here, we will demonstrate how to select an entire row. . Following is a dataset that we will use to demonstrate our point.

Let’s walk through the following steps to select an entire row in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Selecting_row_entire()
ActiveSheet.ListObjects("Table_ref_8").ListRows(3).Range.Select
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will select an entire row as shown below.

12. Resize Range of a Table

Now, we will illustrate how to resize the range of a table. For the purpose of demonstrating our point, we have provided a dataset below.

Let’s walk through the following steps to resize the range of a table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub resize()
Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("Table_ref_5")
Dim Rng As Range
Set Rng = Range("B4:C8")
Table1.Resize Rng
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will resize the range of a table as shown below.

13. Changing Table Style

We will demonstrate how to change the style of a table here. Following is a dataset that we will use to demonstrate our point to reference a table with Excel VBA.

Let’s walk through the following steps to change the table style in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub ChangingTable1_Style()
ActiveSheet.ListObjects("Table_ref_12").TableStyle = "TableStyleLight15"
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will change the table style as shown below.

14. Set Default Table Style

The default table style will be demonstrated here. Following is a dataset that we will use to demonstrate our point to reference a table with Excel VBA.

Let’s walk through the following steps to set the default table style in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Setting_Default_Table1_Style()
ActiveWorkbook.DefaultTableStyle = "TableStyleMedium2"
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will set the default table style as shown below.

15. Loop Through All Cells in a Table

In this example, we will show how to loop through all cells in the table. To loop through each row of a table with VBA, use ListRows.Count property.  For the purpose of demonstrating our point to reference a table with Excel VBA, we have provided a dataset below.

Let’s walk through the following steps to loop through all cells in the table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Loopingthrough()
Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("Table_ref_14")
For i = 1 To Table1.ListRows.Count
    Table1.ListRows(i).Range.Select
Next i
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will loop through all cells in the table as shown below.

16. Make Table Header Visible

Now, we will illustrate how to make the table header visible.  For the purpose of demonstrating our point to reference a table with Excel VBA., we have provided a dataset below.

Let’s walk through the following steps to make the table header visible in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Table_header_visible()
ActiveSheet.ListObjects("Table_ref_15").ShowHeaders = True
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will make the table header visible as shown below.

17. Counting Rows and Columns

Our next step is to demonstrate how to count the rows and columns from a table. Following is a dataset that we will use to demonstrate our point.

Let’s walk through the following steps to count rows and columns from a table in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub counting_rows_columns()
 rowNumber = ActiveSheet.ListObjects("Table_ref_16").ListRows.Count
columnNumber = ActiveSheet.ListObjects("Table_ref_16").ListColumns.Count
MsgBox "Number of Rows: " & rowNumber & vbCrLf & _
        "Number of Columns: " & columnNumber
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will count rows from a table as shown below.
• This will count rows from a table as shown below.

18. Check If There Is Any Table in Worksheet

Here, we will demonstrate how to check if there is any table in the worksheet. For the purpose of demonstrating our point to reference a table with Excel VBA, we have provided a dataset below.

Let’s walk through the following steps to check if there is any table in the worksheet in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Checking_If1_Table1_Exists()
Dim ws1 As Worksheet
Dim tbl1 As ListObject
Dim tbl1Name As String
Dim tbl1Exists As Boolean
tbl1Name = "Table_ref_17"
For Each ws1 In ActiveWorkbook.Worksheets
    For Each tbl1 In ws1.ListObjects
        If tbl1.Name = tb1lName Then
            tb1lExists = True
        End If
    Next tbl1
Next ws1
If tbl1Exists = True Then
    MsgBox "Table " & tb1lName & " Doesn’t Exists."
Else
    MsgBox "Table " & tbl1Name & " Exists."
End If
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will check if there is any table in the worksheet as shown below.

19. Convert a Table to Normal Range

Here is an example of how a table can be converted into a normal range. Following is a dataset that we will use to demonstrate our point to reference a table with Excel VBA.

Let’s walk through the following steps to convert a table to a normal range in Excel.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub Converting_Table1_To_NormalRange()
ActiveSheet.ListObjects("Table_ref_11").Unlist
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will convert a table to a normal range as shown below.

Read More: Excel VBA to Create Table from Range

20. Delete a Table

Here, we will demonstrate how to delete a table. Following is a dataset that we will use to demonstrate our point to reference a table with Excel VBA.

Let’s walk through the following steps to delete a table in Excel with VBA code.

📌 Steps:

• VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

• As a result, a new module will be created. Now select the module if it isn’t already selected. Then write down the following code in it.

Sub delete_table_entire()
Dim Table1 As ListObject
Set Table1 = ActiveSheet.ListObjects("Table_ref_10")
Table1.Delete
End Sub

• Next, save the code.
• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

• Therefore, this will delete a table as shown below.

Download Practice Workbook

Conclusion

That’s the end of today’s session. I strongly believe that from now, you may be able to reference a table with Excel VBA. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!

Related Articles

• Reference Table Column by Name with VBA in Excel
• Excel VBA: Insert Data into Table
• How to Compare Two Tables for Differences with Excel VBA