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 or 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.
In this article, I wanted to bring all the common ways of referencing table data with VBA into one 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!
Section Quick Links
- Excel Tables Overview
- Selecting Areas Of A Table With VBA
- Inserting Rows and Columns Into The Table
- Deleting Parts Of A Table
- Deleting/Clearing The Data In A Table
- Loop Through Each Table Column Or Row
- Looking Up Values Within A Table
- Apply A Sort Order To A Table Column
- Reading Table Data Into An Array Variable
- Resizing A Table
- Change All Table Column’s Total Row Calculations
- Getting To The ActiveTable
- Additional Articles
Excel Tables Overview
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 that 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 a 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
The below infographic will help you visualize the different parts of a Table object through the lens of the VBA coding language.
These parts of a ListObject Table include:
How Do I Find Existing Tables?
Tables can be a little tricky to find if you are not familiar working with them because they can blend in very well with the spreadsheet depending on the formatting that has been applied.
Let’s look at 4 different ways you can determine if you are working with cells in a Table Object.
1. The Table Design Tab Appears
If you click within a cell that is part of an Excel Table, you will immediately see the Table Design tab appear in the Ribbon. This is a contextual tab, which means it only appears when a specific object is selected on your spreadsheet (a similar tab appears when Pivot Tables or Shapes are selected on a spreadsheet).
This is a very quick tell-tail sign that the cell you are working on is part of a Table Object.
2. The Blue Corner Indicator
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 easily missed due to its small size!
3. Use Excel’s Name Manager
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.
4. VBA Code To Check If Cell Is In A ListObject Table
There may be instances when you need to determine if a certain cell resides within a ListObject (Table). The below VBA code shows you how you can perform a test to see if the ActiveCell (selected cell) is part of any Excel Table on the spreadsheet.
Sub IsActiveCellInTable() 'PURPOSE: Determine if the current selected cell is part of an Excel Table 'SOURCE: www.TheSpreadsheetGuru.com Dim TestForTable As String 'Test To See If Cell Is Within A Table On Error Resume Next TestForTable = ActiveCell.ListObject.Name On Error GoTo 0 'Determine Results of Test If TestForTable <> "" Then 'ActiveCell is within a ListObject Table MsgBox "Cell is part of the table named: " & TestForTable Else 'ActiveCell is NOT within a ListObject Table MsgBox "Cell is not part of any table" End If End Sub
This is a great validation test if you are creating code that allows the user to manipulate an Excel table. I’ve used this many times to create buttons that allow users to insert or delete specific rows within a table based on where they select on a password-protected sheet.
Selecting Areas of a Table with VBA
|Table Header Row||ActiveSheet.ListObjects(“Table1”).HeaderRowRange.Select|
|Third Column (Data Only)||ActiveSheet.ListObjects(“Table1”).ListColumns(3).DataBodyRange.Select|
|Select Row 4 of Table Data||ActiveSheet.ListObjects(“Table1”).ListRows(4).Range.Select|
|Select 3rd Heading||ActiveSheet.ListObjects(“Table1”).HeaderRowRange(3).Select|
|Select Data point in Row 3, Column 2||ActiveSheet.ListObjects(“Table1”).DataBodyRange(3, 2).Select|
Inserting Rows and Columns into the Table
|Insert A New Column 4||ActiveSheet.ListObjects(“Table1”).ListColumns.Add Position:=4|
|Insert Column at End of Table||ActiveSheet.ListObjects(“Table1”).ListColumns.Add|
|Insert Row Above Row 5||ActiveSheet.ListObjects(“Table1”).ListRows.Add (5)|
|Add Row To Bottom of Table||ActiveSheet.ListObjects(“Table1”).ListRows.Add AlwaysInsert:= True|
|Add Totals Row||ActiveSheet.ListObjects(“Table1”).ShowTotals = True|
Deleting Various 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/Clearing The Data In A 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
If you have formulas in your table, you may want to keep those intact. The following modification will just remove constant values from the remaining first row in the Table Object.
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 (retaining formulas) tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).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
Apply Sort To Column In A Table
You may find yourself needing to sort your Table data in either Ascending or Descending order. The following VBA code will show you how to sort a column in your ListObject Table in either order.
Sub SortTableColumn() 'PUPOSE: Sort Table in Ascending/Descending Order 'SOURCE: www.TheSpreadsheetGuru.com Dim tbl As ListObject Dim SortOrder As Integer 'Choose Sort Order SortOrder = xlAscending '(or xlDescending) 'Store Desired Excel Table to a variable Set tbl = ActiveSheet.ListObjects("Table1") 'Clear Any Prior Sorting tbl.Sort.SortFields.Clear 'Apply A Sort on Column 1 of Table tbl.Sort.SortFields.Add2 _ Key:=tbl.ListColumns(1).Range, _ SortOn:=xlSortOnValues, _ Order:=SortOrder, _ DataOption:=xlSortNormal 'Sort Options (if you want to change from default) tbl.Sort.Header = xlYes tbl.Sort.MatchCase = False tbl.Sort.Orientation = xlTopToBottom tbl.Sort.SortMethod = xlPinYin 'Apply the Sort to the Table tbl.Sort.Apply End Sub
While the above VBA code has all the potential options written out for you to tweak, most of the time you will not need to stray away from the default sorting options.
Below is the same code, but I’ve removed all the settings whose default values you likely don’t need to change.
Sub SortTableColumn_Simple() 'PUPOSE: Sort Table in Ascending/Descending Order 'SOURCE: www.TheSpreadsheetGuru.com Dim tbl As ListObject Dim SortOrder As Integer 'Choose Sort Order SortOrder = xlDescending '(or xlAscending) 'Store Desired Excel Table to a variable Set tbl = ActiveSheet.ListObjects("Table1") 'Clear Any Prior Sorting tbl.Sort.SortFields.Clear 'Apply A Sort on Column 1 of Table tbl.Sort.SortFields.Add2 _ Key:=tbl.ListColumns(1).Range, _ Order:=SortOrder 'Apply the Sort to the Table tbl.Sort.Apply End Sub
Looking Up Values Within A Table
If you are storing values inside a Table, there may be scenarios where you wish to look up or find a value. There are many different lookup scenarios one might have, but for simplicity, I will provide a generic example. The following code looks to find an ID string within a specific table’s first column and returns that ID’s table row number. Hopefully, you can use the logic within this example and apply it to your specific needs.
Sub LookupTableValue() Dim tbl As ListObject Dim FoundCell As Range Dim LookupValue As String 'Lookup Value LookupValue = "ID-123" 'Store Table Object to a variable Set tbl = ActiveSheet.ListObjects("Table1") 'Attempt to find value in Table's first Column On Error Resume Next Set FoundCell = tbl.DataBodyRange.Columns(1).Find(LookupValue, LookAt:=xlWhole) On Error GoTo 0 'Return Table Row number if value is found If Not FoundCell Is Nothing Then MsgBox "Found in table row: " & _ tbl.ListRows(FoundCell.Row - tbl.HeaderRowRange.Row).Index Else MsgBox "Value not found" End If End Sub
Store Table Data In 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
One 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 your macros. The below code shows you how to load 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 of examples showing 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(tbl.Name & "[#All]").Resize(tbl.Range.Rows.Count + 10, tbl.Range.Columns.Count) tbl.Resize rng End Sub
Change All Table 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 the ActiveTable
There may be instances where you want to make a personal macro that formats your selected table in a certain way or 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
Visual Learner? Download My Example Workbook
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 keep googling about Excel Tables whenever something slips your mind.
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
More Interesting Articles About Excel Tables
- Copy & Paste An Excel Table Into Microsoft Word With VBA
- Loop Through All Tables In Your Excel Workbook or Worksheet
- VBA to Populate Userform Combobox from Excel Table
- Insert & Delete Table Rows With Worksheet Protection
- Function To Determine If Column Heading Exists Inside Excel Table
Anything Else About Tables I Missed?
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 are 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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Function To Determine If Column Heading Exists Inside Excel Table
What This VBA Code Does This VBA function tests to see if a specific column heading name already exists inside...
Resolve Extremely Slow Pasting Into Excel Tables
Stop The Madness! One thing that has always annoyed me with Excel Tables, is how slow it can be to...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.