The Ultimate Guide to Listobjects in VBA

“Listobject” is a feature in Excel VBA that refers to the tables of data in the Excel sheets. We can play around the tables with the rows, columns, format, color, font styles, etc. using this feature in our VBA code.

Before trying to create and use Listobjects through VBA let us understand the parts of a table that we will reference in our code.

Here’s an example table:

NameAnandSanjanaMithulSanyoBharatJohnJames
English56763487236789
Maths56786534879854
Science41738445566587
Social63769865457645
Total216303281231211306275

Let us assume that the above table is a Listobject here.  The parts of a Listobject/table are:

  1. Range
  2. ListRows
  3. ListColumns
  4. HeaderRowRange
  5. DataBodyRange
  6. TotalsRowRange
Graph illustrating ListColumn, HeaderRowRange, ListRow, and Range
Graph illustrating DataBodyRange and TotalsRowRange

Contents

Identify Whether the Range You See is a Structured Table

When you select a cell in the range you doubt to be a table, the Table Design menu appears automatically if the selected cell is already a part of a structured table/Listobject. It is also indicated by a small tail icon at the bottom right corner of the table.

The table in the image below is a Listobject.

Screengrab illustrating the Table Design feature including the Selected cell is part of the table and A small tail icon and the bottom right corner of a table

In the image below, though a cell of a table range is selected we do not see the “Table Design” menu which confirms that this range of cells is not defined as a Listobject/a table.

Screengrab of a table showcasing the Wonders of the World

To Find the Name of the Structured Table

The Name Manager option under the “Formulas” menu can help us with a list of tables/objects available in the workbook along with their names.

Now the name of the table that is displayed in the image is Table4.

The Formulas and Name Manger function in Excel

Another method is to select a cell in the table. The “Table Design” tab will appear. The properties group in the tab will display the name if the table to which the selected cell belongs.

Selecting a cell in the table in the Table Design tab.

Using the methods mentioned above, we have learned to identify if a selected cell belongs to a defined/structured table. Now, we will do the same using code.

Sub IsPartofTable()
'PURPOSE: Find if the currently selected cell is part of any Excel Table

Dim CheckforTable As String

'Test To See If Cell Is Within A Table
  On Error Resume Next
  CheckforTable = ActiveCell.ListObject.name
  On Error GoTo 0

'Determine the results of running the code above
  If CheckforTable <> "" Then
    'ActiveCell is a part of a ListObject Table if the variable has a value
      MsgBox "The selected cell is part of the table named: " & CheckforTable
  Else
    'ActiveCell is NOT a part of a ListObject Table if the  variable is null
      MsgBox "The selected cell is not a part of any table"
  End If

End Sub

Type the above code in the VBA program window and run it by pressing key F5 after selecting a cell from a structured table.

Code Explained:

A variable named “CheckforTable” is created. It is assigned a value. The value is nothing but the name of the Listobject to which the cell belongs.

While this piece of code is run, if the cell really belongs to a Listobject, the name of the Listobject gets stored in the variable. In case the cell does not belong to a Listobject, it becomes a run time error which is handled using the “on error’ statements. If the error statements are hit, the value of the variable remains null. No new value is assigned/fed to it.

So, while the If condition is evaluated, we try to find if the variable is null or if it has some value. If it has a value, we call out the name of the Listobject in a message. If it doesn’t have a value, the flow encounters the else statement and the message that the cell does not belong to a table/Listobject is displayed.

The output/result of running this code is as shown below.

A pop up window on a table that reads "The selected cell is part of the table named: Table4"

When the same code is run while selecting another cell that doesn’t belong to a table, it clearly throws a message that it does not belong to any table.

A pop up window that reads "The selected cell is not a part of any table."

Using VBA to Select Parts of a Table

Once we identify the table name of an existing Listobject Excel table, we can refer to parts of the table/work on it using VBA. The table below has the syntax and examples to refer to parts of the Listobject table.

Selection AreaSyntaxVBA Coding ( with sample values
Full TableActiveSheet.ListObjects( < table name > ).Range.SelectActiveSheet.ListObjects(“StudentTable”).Range.Select
Table’s Header RowActiveSheet.ListObjects( < table name > ).HeaderRowRange.SelectActiveSheet.ListObjects(“StudentTable”).HeaderRowRange.Select
Data in the tableActiveSheet.ListObjects( < table name > ).DataBodyRange.SelectActiveSheet.ListObjects(“StudentTable”).DataBodyRange.Select
Third Column in the tableActiveSheet.ListObjects( < table name > ).ListColumns(< col num>).Range.SelectActiveSheet.ListObjects(“StudentTable”).ListColumns(3).Range.Select
Data in the Third Column of the tableActiveSheet.ListObjects( < table name > ).ListColumns(< col num >).DataBodyRange.SelectActiveSheet.ListObjects(“StudentTable”).ListColumns(3).DataBodyRange.Select
Select fourth row of Table DataActiveSheet.ListObjects( < table name > ).ListRows(< row num >).Range.SelectActiveSheet.ListObjects(“StudentTable”).ListRows(4).Range.Select
Select third Heading of the tableActiveSheet.ListObjects( < table name > ).HeaderRowRange(< row num >).SelectActiveSheet.ListObjects(“StudentTable”).HeaderRowRange(3).Select
Select Data in 3rd row, 4th column of the tableActiveSheet.ListObjects( < table name > ).DataBodyRange(< row num>, <col num>).SelectActiveSheet.ListObjects(“StudentTable”).DataBodyRange(3, 4).Select
Subtotals row in the tableActiveSheet.ListObjects( < table name > ).TotalsRowRange.SelectActiveSheet.ListObjects(“StudentTable”).TotalsRowRange.Select

Insertion of Rows and Columns into the Listobject Table.

Just like how we manually have the facilities to insert columns and rows into a table, using VBA the same can be done on the Listobject tables.

Here are some actions that we generally tend to do on an existing table. The Syntax and sample for the same are displayed in two different columns. The table name used in the sample is “EmployeeDetails.”

ActionSyntaxVBA Coding
Insert A New Column in the 3rd positionActiveSheet.ListObjects(” < table name > “).ListColumns.Add Position:= < position number >ActiveSheet.ListObjects(“EmployeeDetails”).ListColumns.Add Position:=3
Insert a column at the end of the table ( right side)ActiveSheet.ListObjects(” < table name > “).ListColumns.AddActiveSheet.ListObjects(“EmployeeDetails”).ListColumns.Add
Inser a new row above row  # 4ActiveSheet.ListObjects(” < table name > “).ListRows.Add (4)ActiveSheet.ListObjects(“EmployeeDetails”).ListRows.Add (4)
Add a new row at the bottom of the table ( as the last row)ActiveSheet.ListObjects(” < table name > “).ListRows.Add AlwaysInsert:= TrueActiveSheet.ListObjects(“EmployeeDetails”).ListRows.Add AlwaysInsert:= True
Add a Totals RowActiveSheet.ListObjects(” < table name > “).ShowTotals = TrueActiveSheet.ListObjects(“EmployeeDetails”).ShowTotals = True

A Sample Code to Insert One Row at the Bottom and 1 Column on the Right-hand Side at the End

Sub Work_on_listobject()
'PURPOSE: try to insert some rows / columns of a list object

' create a variable to hold a table
Dim tbl As ListObject
' refer to the table listobject
Set tbl = ActiveSheet.ListObjects(&quot;Table4&quot;)
' display the table name again just to ensure that you are on the right table.

MsgBox tbl.name

' to add a row at the end at the bottom
tbl.ListRows.Add

' to add a column at the end on the right side
tbl.ListColumns.Add

End Sub

Output

Column I and Row 7 have been inserted new as a result of running the code above.

Output table with Column I and Row 7 inserted

Code to Insert a Row Below the “4th row” and a Column Before  “Column G”


Sub Work_on_listobject()
'PURPOSE: try to insert some rows / columns of a list object

' create a variable to hold a table
Dim tbl As ListObject
' refer to the table listobject
Set tbl = ActiveSheet.ListObjects(&quot;Table4&quot;)
' display the table name again just to ensure that you are on the right table.

MsgBox tbl.name

' to add a row at the 4th position of the table ( this position does not include the header).
tbl.ListRows.Add (4)

' to add a column at the 7th position
tbl.ListColumns.Add (7)

End Sub

Output:

Please note that in the output, the additional row is inserted in the 4th row of data within the table. If we include the header in the count, then it is the fifth position. So, we can understand that when we use the Listobject.Add() method, we should not consider the header while providing the position number in the parameter.

Output of table where the additional row is inserted in the 4th row of data within the table.

Deletion of Rows and Columns of a Listobject Table

Now, I have added some data to the newly inserted rows and columns. The code below is run to delete the same rows and columns that were just added.


Sub Work_on_listobject()
'PURPOSE: try to delete some rows / columns of a list object

' create a variable to hold a table
Dim tbl As ListObject
' refer to the table listobject
Set tbl = ActiveSheet.ListObjects(&quot;Table4&quot;)
' display the table name again just to ensure that you are on the right table.

MsgBox tbl.name

' to delete a row
tbl.ListRows.Item(4).Delete

' to delete a column
tbl.ListColumns.Item(7).Delete

End Sub

Output

Output table with rows deleted

You may notice that the same newly added row which was in position 4 and newly added column which was displayed in col G have been deleted successfully.

Deleting a Range of Rows

This code can be used to delete a range of rows on the same table.

Let us now make a copy of the three rows in positions 3,4, and 5 of the existing table in positions 6,7, and 8. (positions are calculated without considering the header as explained earlier).

The code will have a range of rows from 6 to 8 to be deleted.


Sub Work_on_listobject()
'PURPOSE: try to delete a range of data in a list object

' create a variable to hold a table
Dim tbl As ListObject
' refer to the table listobject
Set tbl = ActiveSheet.ListObjects(&quot;Table4&quot;)
' display the table name again just to ensure that you are on the right table.
MsgBox tbl.name
' to delete a range of data
tbl.Range.Rows(&quot;6:8&quot;).Delete
End Sub

Output

Three rows of data have been deleted through the code.

Looping Through Rows and Columns of a Listobject Table

Here is a piece of code that can help us adjust the height of rows and width of columns of the named Listobject table.

Sub Listobject_loop()

Dim tbl As ListObject
Dim i As Long

Set tbl = ActiveSheet.ListObjects(&quot;Table4&quot;)

'Looping Through every Column in the listobject Table
  For i = 1 To tbl.ListColumns.Count
    tbl.ListColumns(i).Range.ColumnWidth = 10
  Next i

'Looping Through every row in the listobject Table
  For i = 1 To tbl.Range.Rows.Count
    tbl.Range.Rows(i).RowHeight = 25
  Next i
  
'Looping Through every databody row in the listobject Table
  For i = 1 To tbl.ListRows.Count
    tbl.ListRows(i).Range.RowHeight = 17
  Next i

End Sub

In this code, we first loop through all the Listcolumns of the Listobject table and set their column width to 17.

Next we iterate through all the rows (including the header) of the Listobject table and set each of their “row height” to 25.

Finally, we iterate through all the Listrows of the Listobject table (without the header) and set their “row height” to 17. Now the row height of the columns without the header gets reset from 25 to 17 and the header height remains 25 as it is not a part of this loop.

Height of the Header

Setting the row height in a table

Height of the Other Rows in the Table

Setting the row height of the rest of the rows

Width of All the Columns

Setting the width of a column

Sorting in the Listobject Table Using VBA

Within the table, sorting can be done on 1 or more columns just like how we can manually do it on Excel tables.

Let us apply a sort in the 2nd column of this table (in ascending order)

S.noWonderLocation
1Taj MahalIndia.- Agra
2Chichen ItzaMexico.- Yucatán
3Christ the RedeemerBrazil.- Rio de Janeiro
4ColosseumItaly.- Rome
5Great Wall of ChinaChina
6Machu PicchuCuzco Region
7PetraJordan.-Ma’an Governorate
8Great Pyramid of GizaEgypt.

Sub Sort_Column_LO()

' declare required variables

Dim tbl As ListObject

' Assign the table to the listobject variable
  Set tbl = ActiveSheet.ListObjects(&quot;Table3&quot;)

' Clear Any Existing Sort order
  tbl.Sort.SortFields.Clear
    
' Apply / add A new Sort on Column 2 of Table

    tbl.Sort.SortFields.Add2 _
        Key:=tbl.ListColumns(2).Range, _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    
' Set Sort Options (in case you want to change from default)
  tbl.Sort.Header = xlYes
  tbl.Sort.MatchCase = False
  tbl.Sort.Orientation = xlTopToBottom
  tbl.Sort.SortMethod = xlPinYin

'Apply the designed Sort to the Table
  tbl.Sort.Apply

End Sub

All parameters set in the code other than Key and Sort order are optional and just to change the default settings in a Sort.

Output: The second column is sorted alphabetically in ascending order.

S.noWonderLocation
2Chichen ItzaMexico.- Yucatán
3Christ the RedeemerBrazil.- Rio de Janeiro
4ColosseumItaly.- Rome
8Great Pyramid of GizaEgypt.
5Great Wall of ChinaChina
6Machu PicchuCuzco Region
7PetraJordan.-Ma’an Governorate
1Taj MahalIndia.- Agra

Try the same example with xldescending option. It will sort the column in descending order.

Lookup Values in a Listobject Table

Just like how we use vlookup, hlookup, and xlookup in Excel, we can do the same within the Listobject tables using VBA code.

In this example considering the table below as a Listobject, we will try to find the rating of any specific branch name.

LocationBranchRatingNo of customers
AustraliaBrisbane4.80850
AustraliaGold Coast4.20280
AustraliaMelbourne3.90240
AustraliaPerth3.20340
AustraliaSydney2.95900
IndiaBombay5.00150
IndiaKolkatta4.40100
IndiaChennai4.30200
IndiaDelhi3.30120
USADallas5.0060
USASeattle4.90170
USAChicago4.50145
USANew York2.30300

The table name here is “Bank”

Here is a piece of code in a sub-procedure that tries to find the row number containing the text “Chicago” in the 2nd column.

Sub Lookup_rating()

' declare necessary variables

Dim tbl As ListObject
Dim Found_Cell As Range
Dim Lookup_Value As String
Dim row_num As Integer

' assign value to the lookup_Value variable
  Lookup_Value = &quot;Chicago&quot;

' Assign the table to a listobject variable
  Set tbl = ActiveSheet.ListObjects(&quot;Bank&quot;)

' Try to find value in Table's second Column as the lookup value belongs to that column
  On Error Resume Next
  Set Found_Cell = tbl.DataBodyRange.Columns(2).Find(Lookup_Value, LookAt:=xlWhole)
  On Error GoTo 0

' Return the Table Row number if value is found
  If Not Found_Cell Is Nothing Then
  row_num = tbl.ListRows(Found_Cell.Row - tbl.HeaderRowRange.Row).Index
    MsgBox &quot;Value is found in table's row # : &quot; &amp; row_num
  Else
    MsgBox &quot;Value is not found&quot;
  End If

End Sub

Output:

The value “Chicago” is in the 2nd column 12th row of the table’s body. Hence the number 12 is displayed as a part of the output message box.

Pop-up window that reads "Value is found in table's row # : 12"

Resize the Listobject Table By Incrementing the # of Rows and Cols

Let us try to resize the same Bank table using VBA code now. We will increase the number of rows to 18 and columns to 7.

Sub Resize_listobject()

' declare variables
Dim rng As Range
Dim tbl As ListObject

' Set the resize range to 20 rows and 9 cols
  Set rng = Range(&quot;Bank[#All]&quot;).Resize(20, 9)
  
' Resize Table to 20 rows and 7 columns
  ActiveSheet.ListObjects(&quot;Bank&quot;).Resize rng
  
' Expand Table size by 10 rows
  Set tbl = ActiveSheet.ListObjects(&quot;Bank&quot;)

End Sub

Using this code, we are setting the size of the Bank table to have 20 rows and 9 columns.

The final table looks like this:

Output of code where the size of the Bank table is set to have 20 rows and 9 columns.

We can also use the code below to reduce or increase the number of rows and columns by a specific number.

In this code, I will reduce the number of columns by 2 and the number of rows by 3.


Sub Resize_listobject()

' declare variables
Dim rng As Range
Dim tbl As ListObject

'' Assign the listobject to the variable
  Set tbl = ActiveSheet.ListObjects(&quot;Bank&quot;)

' Set the resize range to existing # of cols -2 and row to existing no of rows -2
  

  Set rng = Range(tbl.name &amp; &quot;[#All]&quot;).Resize(tbl.Range.Rows.Count - 2, tbl.Range.Columns.Count - 3)
  tbl.Resize rng

End Sub

Here’s the output:

Output of the code to reduce or increase the number of rows and columns by a specific number.

Conclusion

Table objects can be used as normal tables in Excel and any operations showcased in this article can be done using VBA on the Listobjects. There is much more to play around with the tables with reference to the numeric data stored and the calculations within the totals row. This feature can be of much help when it comes to the automation of reports and graphs. These tables/Listobjects can be created/managed using standardized VBA code. To convert a normal Excel range to a proper table object, I generally use the ”Format as Table” option in the Home tab. It also makes the table look good and colorful. We can also do the same using VBA code. So, are you waiting for? Go ahead and try to use a Listobject table and see the difference for yourself.

Leave a Reply

Your email address will not be published. Required fields are marked *