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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


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).


20 Suitable Examples to Use Table Reference in Excel with VBA Code

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. Click here to see how you can show the Developer tab 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.

select an entire table to Reference a Table with Excel VBA

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.

insert module to Reference a Table with Excel VBA

  • 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.

show the output to reference an entire table with VBA

Read More: How to Use Cell References in Excel Formula (All Possible Ways)


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.

copy an entire table 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.

insert module to Reference a Table with Excel VBA

  • 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.

run VBA code

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

show the output as a Reference a Table with Excel VBA

Read More: Cell Reference in Excel VBA (8 Examples)


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.

reference a column 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.

open developer tab to Reference a Table with Excel VBA

  • 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.

run vba code to Reference a Table with Excel VBA

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

show the output


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.

get the value to Reference a Table with Excel VBA

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.

insert a developer tab

  • 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.

insert module to Reference a Table with Excel VBA

  • 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.

show the output


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.

entering a row to Reference a Table with Excel VBA

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.

insert module to Reference a Table with Excel VBA

  • 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.

run vba code to Reference a Table with Excel VBA

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

get the output

Read More: How to Reference a Cell from a Different Worksheet in Excel


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.

entering a column to Reference a Table with Excel VBA

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.

insert module

  • 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.

get the output


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.

filtering a table to Reference a Table with Excel VBA

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.

inserting module

  • 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.

filtering a Table with Excel VBA for reference purpose

Read More: How to Reference Text in Another Cell in Excel (14 Ways)


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.

delete row and column to Reference a Table with Excel VBA

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.

run VBA code

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

get the output


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:

 select data from a table to Reference a Table with Excel VBA

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.

insert module

  • 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.

get the output


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.

selecting column header to Reference a Table with Excel VBA

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.

run the VBA code

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

show the output


Similar Readings


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.

select entire table to Reference a Table with Excel VBA

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.

run VBA to Reference a Table

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

get the output


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.

resize range of a table to Reference a Table with Excel VBA

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.

inserting module

  • 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.

get the output


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.

change table style 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.

run vba code

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

get the output


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.

set default table style 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.

open developer tab

  • 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.

show the output


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.

loop through all cells to Reference a Table with Excel VBA

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.

run VBA

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

show the output


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.

make table header visible to Reference a Table with Excel VBA

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.

run vba code

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

get the output


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.

counting rows and columns to Reference a Table with Excel VBA

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.

insert mdoule

  • 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.

show the output

Read More: Excel VBA Examples with Cell Reference by Row and Column Number


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.

checking number worksheet to Reference a Table with Excel VBA

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.

run the code

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

get the output

Read More: Reference Another Sheet in Excel (3 Methods)


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.

convert a table to normal range 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.

run vba code

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

get the output


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.

delete a table 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.

delete the whole table


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.

Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo