The VBA Guide To ListObject Excel Tables

The VBA Guide To ListObject Excel Tables

All About The Tables

For a data analyst, Excel Tables are a necessity!  They are the most efficient way to organize your raw data and refer to data that contracts/expands on a regular basis.  Likewise, Excel tables can be extremely useful in combination with VBA.  I personally use data tables as a way to store user settings without having to modify any VBA code.  You can see examples of this in my Exporter Template where I use tables to store worksheet names and email addresses.  In this article I wanted to bring all the common ways of referencing table data with VBA into on place.  Hopefully this will serve as a guide you can come back to again and again so you can easily and efficiently incorporate tables into your VBA macro coding. Enjoy!

What Is A Table?

A Table is simply a structured range where you can refer to different sections that are automatically mapped out (such as the Header Row or the column below the header "Amount"). Tables are an amazing feature the Microsoft added into Excel because they not only structure your data, but they also expand with your data as it grows. And if there is one thing you should know about creating a spreadsheet, it would be that making it as DYNAMIC as possible is always good thing!

You can quickly create a Table by highlighting a range (with proper headings) and using the keyboard shortcut ctrl + t. You can also navigate to the Insert tab and select the Table button within the Tables group.

The Parts of A Table

Below are some images to help you visualize the different structured parts of a Table in VBA terms.

How Do I Find Tables?

Tables can be a little tricky to find if you are completely new to them because they can blend in very well. There is a small little indicator at the bottom right cell of a Table range to indicate there is a table. As you can see in the image below, this indicator can be very simple to find, but also can be a hassle!

Table Indicator Corner Tag

Another great way to find a table (and its name) is to go into the Name Manager. You can get to the name manager by navigating to the Formulas tab and clicking the Name Manager button inside the Defined Names group.

By using the Filter menu in the right-hand corner of the Name Manager, you can narrow down your name list to just the Tables within the Workbook. The Name Manager will show you exactly where the tables are within the spreadsheet and also what the Table names are.

Find Table Information With Name Manager
 

Selecting Areas Of A Table With VBA

SelectVBA Coding
Entire TableActiveSheet.ListObjects("Table1").Range.Select
Table Header RowActiveSheet.ListObjects("Table1").HeaderRowRange.Select
Table DataActiveSheet.ListObjects("Table1").DataBodyRange.Select
Third ColumnActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select
Third Column (Data Only)ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select
Select Row 4 of Table DataActiveSheet.ListObjects("Table1").ListRows(4).Range.Select
Select 3rd HeadingActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select
Select Data point in Row 3, Column 2ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2).Select
SubtotalsActiveSheet.ListObjects("Table1").TotalsRowRange.Select

Inserting Rows and Columns Into The Table

SelectVBA Coding
Insert A New Column 4ActiveSheet.ListObjects("Table1").ListColumns.Add Position:=4
Insert Column at End of TableActiveSheet.ListObjects("Table1").ListColumns.Add
Insert Row Above Row 5ActiveSheet.ListObjects("Table1").ListRows.Add (5)
Add Row To Bottom of TableActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:= True
Add Totals RowActiveSheet.ListObjects("Table1").ShowTotals = True

Deleting Parts Of A Table

Sub RemovePartsOfTable()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Remove 3rd Column
  tbl.ListColumns(3).Delete

'Remove 4th DataBody Row
  tbl.ListRows(4).Delete

'Remove 3rd through 5th DataBody Rows
  tbl.Range.Rows("3:5").Delete

'Remove Totals Row
  tbl.TotalsRowRange.Delete

End Sub

Deleting The Entire Table

Delete all data rows from a table (except the first row)

Sub ResetTable()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Delete all table rows except first row
  With tbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With

'Clear out data from first table row
  tbl.DataBodyRange.Rows(1).ClearContents

End Sub

Loop Through Each Table Column Or Row

Sub LoopingThroughTable()

Dim tbl As ListObject
Dim x As Long

Set tbl = ActiveSheet.ListObjects("Table1")

'Loop Through Each Column in Table
  For x = 1 To tbl.ListColumns.Count
    tbl.ListColumns(x).Range.ColumnWidth = 8
  Next x

'Loop Through Every Row in Table
  For x = 1 To tbl.Range.Rows.Count
    tbl.Range.Rows(x).RowHeight = 20
  Next x
  
'Loop Through Each DataBody Row in Table
  For x = 1 To tbl.ListRows.Count
    tbl.ListRows(x).Range.RowHeight = 15
  Next x

End Sub

Reading Table Data Into An Array Variable

Pulling in data from tables is a great tactic to incorporate in your VBA coding.  Tables are ideal because they

  • Are always structured the same
  • Can be moved anywhere on the spreadsheet without affecting your code
  • Automatically adjust their range size 

On example of using Tables as a data source in a macro is shown in one of my Code Vault snippets which allows you to filter your data based on the words in a specified table.  There are tons of different ways you can use tables to store settings and preferences dynamically for you macros. The below code shows you how to load in data from a single column and a multi column table.

Single Column Table

Sub SingleColumnTable_To_Array()

Dim myTable As ListObject
Dim myArray As Variant
Dim TempArray As Variant
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")

'Create Array List from Table
  TempArray = myTable.DataBodyRange
  
'Convert from vertical to horizontal array list
  myArray = Application.Transpose(TempArray)

'Loop through each item in the Table Array (displayed in Immediate Window [ctrl + g])
  For x = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(x)
  Next x
  
End Sub

Multiple Column Table

Sub MultiColumnTable_To_Array()

Dim myTable As ListObject
Dim myArray As Variant
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")

'Create Array List from Table
  myArray = myTable.DataBodyRange

'Loop through each item in Third Column of Table (displayed in Immediate Window [ctrl + g])
  For x = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(x, 3)
  Next x
  
End Sub

Resizing A Table

If needed, you can resize a table's dimensions by declaring a new range area for the Excel table to shrink or expand.  Below are a couple examples of how you can perform this sort of size adjustment.

(A special thanks to Peter Bartholomew for requesting this on LinkedIn)

Sub ResizeTable()

Dim rng As Range
Dim tbl As ListObject

'Resize Table to 7 rows and 5 columns
  Set rng = Range("Table1[#All]").Resize(7, 5)
  
  ActiveSheet.ListObjects("Table1").Resize rng
  
  
'Expand Table size by 10 rows
  Set tbl = ActiveSheet.ListObjects("Table1")
  
  Set rng = Range("Table1[#All]").Resize(tbl.Range.Rows.Count + 10, tbl.Range.Columns.Count)
  
  tbl.Resize rng

End Sub

Clearing Out A Table

There may be instances where you want to remove all the body data from your table (ie reset it).  A problem may arise if you only have one row of data in your table.  Since a table by definition must have one header row and at least one data row, you will get an error message if your code tries to delete a DataBodyRange with no data in it.  For this reason we need to account for both scenarios.

Sub RemoveTableBodyData()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Delete Table's Body Data
  If tbl.ListRows.Count >= 1 Then
    tbl.DataBodyRange.Delete
  End If
  
End Sub

Change All Table Column's Total Row Calculations

Sub ChangeAllColumnTotals()

Dim tbl As ListObject
Dim CalcType As Integer
Dim x As Long

Set tbl = ActiveSheet.ListObjects("Table1")

'What calculation should the Totals Row Have?
  CalcType = 1 'or: xlTotalsCalculationSum

'Loop Through All Table Columns
  For x = 1 To tbl.ListColumns.Count
    tbl.ListColumns(x).TotalsCalculation = CalcType
  Next x

'___________________________________________
'Members of xlTotalsCalculation
    'Enum       Calculation
    ' 0           None
    ' 1           Sum
    ' 2           Average
    ' 3           Count
    ' 4           Count Numbers
    ' 5           Min
    ' 6           Max
    ' 7           Std Deviation
    ' 8           Var
    ' 9           Custom
'___________________________________________

End Sub

Getting To The ActiveTable

There may be instances were you want to make a personal macro that formats your selected table in a certain way our adds certain calculation columns.  Since the Excel developers didn't create an ActiveTable command in their VBA language, you have no straightforward way of manipulating a user-selected table.  But with a little creativity you can make your own ActiveTable ListObject variable and do whatever you want with the selected table!

Sub DetermineActiveTable()

Dim SelectedCell As Range
Dim TableName As String
Dim ActiveTable As ListObject

Set SelectedCell = ActiveCell

'Determine if ActiveCell is inside a Table
  On Error GoTo NoTableSelected
    TableName = SelectedCell.ListObject.Name
    Set ActiveTable = ActiveSheet.ListObjects(TableName)
  On Error GoTo 0

'Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
  ActiveTable.ListRows.Add AlwaysInsert:=True
  
Exit Sub

'Error Handling
NoTableSelected:
  MsgBox "There is no Table currently selected!", vbCritical

End Sub

Are You A Visual Learner? Download My Example Workbook

Screenshot from one of the tabs in the downloadable file

After many requests, I put together a fun little interactive workbook that will show you how a bunch of the code described in this article actually works on a spreadsheet.  It also serves as a good reference that you can save to your computer so you don't have to keeping googling about Excel Tables whenever something slips your mind.  

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section

 

Other Good VBA Table Resources

JKP-ADS.com - Working With Tables In Excel 2013, 2010 And 2007 (VBA)

AnalysisTabs.com - Tables in Excel VBA

dataprose.com - Listing Toward ListObjects [Excel Tables]

Anything Else?

Did you come to this page trying to find out how to do something with VBA and Excel tables and it wasn't covered? If that is the case, let me know what you were looking for in the comment section below.  If it makes sense to add it to this guide and will definitely add it to the content.  I look forward to reading your thoughts and/or recommendations!


How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)