VBA ListObjects

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What are ListObjects in VBA?

In a Table, normally, what we see is a data set. Still, in VBA terminology, there are many more such as there is the range of the total data list range. The column is known as the list column, the row as the list row, and so on. To access these properties, we have an inbuilt function known as ListObjects, used with the worksheet function.

VBA ListObject is a way of referring to the Excel tables while writing the VBA code. Using VBA LISTOBJECTS, we can create and delete tables and play around with Excel Tables in VBA code. However, Excel Tables are tricky for beginners, and even to an extent, intermediate-level users find it difficult to work with Tables. Since this article talks about referencing excel tables in VBA coding, you should have good knowledge about Tables in Excel.

When the data converts to tables, we may no longer work with a range of cells. Rather, we need to work with table ranges. So, this article will show you how to work with Excel Tables to write VBA codes efficiently.

Table of contents
VBA-ListObjects

Create Table Format Using ListObjects in Excel VBA

For example, look at the below Excel data.

List Object Excel Data

Using the VBA ListObject code, we will create a table format for this data.

  • For this data, first, we need to find the last used row and column, so define two variables to find this.

Code:

Sub List_Objects_Example1()

    Dim LR As Long
    Dim LC As Long

End Sub
VBA List Object Example 1.2
  • To find the last used row and column use the below code.

Code:

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
VBA List Object Example 1.3
  • Now define one more variable to hold the reference of the data.

Code:

Dim Rng As Range
VBA List Object Example 1.4
  • Now set the reference to this variable by using the below code.

Code:

Set Rng = Cells(1, 1).Resize(LR, LC)
VBA List Object Example 1.5

We need to use the VBA "ListObject.Add" method to create a table; below is the same syntax.

ListObject.Add (Source, XlListObjectHasHeaders, Destination, TableStyleName)

Source: TThis is nothing for which range of cells we are inserting the table. So we can supply two arguments here, i.e., â€śxlSrcRange” and â€śxlSrcExternal.”

XlListObjectHasHeaders: If the table inserting data has headers or not. If yes, we can provide â€śxlYes.” If not, we can provide â€śxlNo.”

Destination: This is nothing but our data range.

Table Style: We can provide styles if you want to apply any table style.

  • Now in the active sheet, we are creating the table, so the below code would create a table for us.

Code:

Dim Ws As Worksheet
Set Ws = ActiveSheet

Ws.ListObjects.Add xlSrcRange, xllistobjecthasheaders:=xlYes, Destination:=Rng
VBA List Object Example 1.6
  • After this, we need to give a name to this table.

Code:

Ws.ListObjects(1).name = "EmpTable"
VBA List Object Example 1.7
  • Below is the full code for your reference.

Code:

Sub List_Objects_Example1()

    Dim LR As Long
    Dim LC As Long

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column

    Dim Rng As Range
    Set Rng = Cells(1, 1).Resize(LR, LC)

    Dim Ws As Worksheet
    Set Ws = ActiveSheet

    Ws.ListObjects.Add xlSrcRange, xllistobjecthasheaders:=xlYes, Destination:=Rng
    Ws.ListObjects(1).name = "EmpTable"

End Sub

Let us run the code and see the magic.

VBA List Object Example 1.8

It has created the table to the mentioned data and given the table name "EmpTable."

Formatting Excel Tables with VBA ListObjects

Once we create the Excel table, we can work with tables using the VBA ListObject collection.

  • First, define the variable as “ListObject.”

Code:

Sub List_Objects_Example2()

    Dim MyTable As ListObject

End Sub
VBA List Object Example 2.1
  • Now, set the reference to this variable by using the table name.

Code:

Sub List_Objects_Example2()

    Dim MyTable As ListObject
    Set MyTable = ActiveSheet.ListObjects("EmpTable")

End Sub
Set Reference

Now, the variable "MyTable" holds the reference for the table "EmpTable."

  • Enter the variable name and put a dot to see the properties and methods of the VBA ListObject.
List Object Properties

For example, if we want to select the entire table, then we need to use the “Range” object, and under this, we need to use the “Select” method.

Code:

MyTable.Range.Select
List Object to Select Entire Table

It would select the entire data table, including the heading.

  • If you want to select only the table contents without headers, we need to use “DataBodyRange.”

Code:

MyTable.DataBodyRange.Select
To Select Table Content

Like this, we can play around with tables.

  • Below is the list of activity codes for your reference.

Code:

Sub List_Objects_Example2()

    Dim MyTable As ListObject
    Set MyTable = ActiveSheet.ListObjects("EmpTable")

    MyTable.DataBodyRange.Select
    'To Select data range without headers

    MyTable.Range.Select
    'To Select data range with headers

    MyTable.HeaderRowRange.Select
    'To Select table header rows

    MyTable.ListColumns(2).Range.Select
    'To select column 2 including header

    MyTable.ListColumns(2).DataBodyRange.Select
    'To select column 2 without header

End Sub

Like this, we can use the "ListObject" collection to play around with Excel tables.

Things to Remember

  • VBA ListObject is the collection of objects to reference excel tables.
  • To access the ListObject collection, we need to specify what worksheet we are referring to.