WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA ListObjects

VBA ListObjects

What are ListObjects in VBA?

In a table normally what we see is a data set but in VBA terminology there are much more such as there is range of the total data list range, the column is known as the list column and row is known as the list row and so on, so in order to access this properties we have an inbuilt function known as Listobjects and which is used with the worksheet function.

VBA ListObject is a way of referring to the excel tables while writing the VBA code. By using VBA LISTOBJECTS, we can create,  delete table, and totally we can play around with excel tables in VBA code. Excel Tables are tricky, 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, it is better you have good knowledge about tables in excel.

When the data is converted to tables, we no longer work with a range of cells. Rather, we need to work with table ranges, so in this article, we will show you how to work with excel tables to write VBA codes efficiently.

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.

You can download this VBA ListObjects Excel Template here – VBA ListObjects Excel Template
  • For this data first, we need to find what is the last used row & 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

Now we need to use VBA “ListObject.Add” method to create a table, and below is the syntax of the same.

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

Source: This 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: If you want to apply any table style, we can provide styles.

  • Ok, 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

Ok, let’s 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 as “EmpTable.”

Formatting Excel Tables with VBA ListObjects

Once the Excel table has been created, we can work with tables by using 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

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

  • If you want to select only the contents of the table without headers, then 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 ListObject collection first, we need to specify what worksheet we are referring to is.

Recommended Articles

This has been a guide to VBA ListObject. Here we discuss how to use VBA ListObject.Add method to create a table in excel using examples downloadable excel sheet. You can learn more from the following VBA articles –

  • VBA Find Next
  • VBA Object Required
  • CreateObject in VBA
  • GetObject in VBA
1 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA ListObjects Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More