Tables are one of the most powerful features of Excel. Controlling them using VBA provides a way to automate that power, which generates a double benefit
Excel likes to store data within tables. The basic structural rules, such as [a] headings must be unique [b] only one header row allowed, make tables compatible with more complex tools. For example, Power Query, Power Pivot, and SharePoint lists all use tables as either a source or an output. Therefore, it is clearly Microsofts intention that we use tables.
However, the biggest benefit to the everyday Excel user is much simpler; if we add new data to the bottom of a table, any formulas referencing the table will automatically expand to include the new data.
Whether you love tables as much as I do or not, this post will help you automate them with VBA.
Tables, as we know them today, first appeared in Excel 2007. This was a replacement for the Lists functionality found in Excel 2003. From a VBA perspective, the document object model [DOM] did not change with the upgraded functionality. So, while we use the term tables in Excel, they are still referred to as ListObjects within VBA.
Download the example file
I recommend you download the example file for this post. Then youll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0009 VBA tables and ListObjects.zip
Structure of a table
Before we get deep into any VBA code, its useful to understand how tables are structured.
Range & Data Body Range
The range is the whole area of the table.
The data body range only includes the rows of data, it excludes the header and totals.
Header and total rows
The header row range is the top row of the table containing the column headers.
The totals row range, if displayed, includes calculations at the bottom of the table.
List columns and list rows
The individual columns are known as list columns.
Each row is known as a list row.
The VBA code in this post details how to manage all these table objects.
Referencing the parts of a table
While you may be tempted to skip this section, I recommend you read it in full and work through the examples. Understanding Excels document object model is the key to reading and writing VBA code. Master this, and your ability to write your own VBA code will be much higher.
Many of the examples in this first section use the select method, this is to illustrate how to reference parts of the table. In reality, you would rarely use the select method.
Select the entire table
The following macro will select the whole table, including the totals and header rows.
Sub SelectTable[] ActiveSheet.ListObjects["myTable"].Range.Select End SubSelect the data within a table
The DataBodyRange excludes the header and totals sections of the table.
Sub SelectTableData[] ActiveSheet.ListObjects["myTable"].DataBodyRange.Select End SubGet a value from an individual cell within a table
The following macro retrieves the table value from row 2, column 4, and displays it in a message box.
Sub GetValueFromTable[] MsgBox ActiveSheet.ListObjects["myTable"].DataBodyRange[2, 4].value End SubSelect an entire column
The macro below shows how to select a column by its position, or by its name.
Sub SelectAnEntireColumn[] 'Select column based on position ActiveSheet.ListObjects["myTable"].ListColumns[2].Range.Select 'Select column based on name ActiveSheet.ListObjects["myTable"].ListColumns["Category"].Range.Select End SubSelect a column [data only]
This is similar to the macro above, but it uses the DataBodyRange to only select the data; it excludes the headers and totals.
Sub SelectColumnData[] 'Select column data based on position ActiveSheet.ListObjects["myTable"].ListColumns[4].DataBodyRange.Select 'Select column data based on name ActiveSheet.ListObjects["myTable"].ListColumns["Category"].DataBodyRange.Select End SubSelect a specific column header
This macro shows how to select the column header cell of the 5th column.
Sub SelectCellInHeader[] ActiveSheet.ListObjects["myTable"].HeaderRowRange[5].Select End SubSelect a specific column within the totals section
This example demonstrates how to select the cell in the totals row of the 3rd column.
Sub SelectCellInTotal[] ActiveSheet.ListObjects["myTable"].TotalsRowRange[3].Select End Sub report this adSelect an entire row of data
The macro below selects the 3rd row of data from the table.
Select the header row
The following macro selects the header section of the table.
Sub SelectHeaderSection[] ActiveSheet.ListObjects["myTable"].HeaderRowRange.Select End SubSelect the totals row
To select the totals row of the table, use the following code.
Sub SelectTotalsSection[] ActiveSheet.ListObjects["myTable"].TotalsRowRange.Select End SubOK, now we know how to reference the parts of a table, its time to get into some more interesting examples.
Creating and converting tables
This section of macros focuses on creating and resizing tables.
Convert selection to a table
The macro below creates a table based on the currently selected region and names it as myTable. The range is referenced as Selection.CurrentRegion, but this can be substituted for any range object.
If youre working along with the example file, this macro will trigger an error, as a table called myTable already exists in the workbook. A new table will still be created with a default name, but the VBA code will error at the renaming step.
Sub ConvertRangeToTable[] tableName As String Dim tableRange As Range Set tableName = "myTable" Set tableRange = Selection.CurrentRegion ActiveSheet.ListObjects.Add[SourceType:=xlSrcRange, _ Source:=tableRange, _ xlListObjectHasHeaders:=xlYes _ ].Name = tableName End SubConvert a table back to a range
This macro will convert a table back to a standard range.
Sub ConvertTableToRange[] ActiveSheet.ListObjects["myTable"].Unlist End SubResize the range of the table
To following macro resizes a table to cell A1 J100.
Sub ResizeTableRange[] ActiveSheet.ListObjects["myTable"].Resize Range["$A$1:$J$100"] End SubTable styles
There are many table formatting options, the most common of which are shown below.
Change the table style
Change the style of a table to an existing pre-defined style.
Sub ChangeTableStyle[] ActiveSheet.ListObjects["myTable"].TableStyle = "TableStyleLight15" End SubTo apply different table styles, the easiest method is to use the macro recorder. The recorded VBA code will include the name of any styles you select.
Get the table style name
Use the following macro to get the name of the style already applied to a table.
Sub GetTableStyleName[] MsgBox ActiveSheet.ListObjects["myTable"].TableStyle End SubApply a style to the first or last column
The first and last columns of a table can be formatted differently using the following macros.
Sub ColumnStyles[] 'Apply special style to first column ActiveSheet.ListObjects["myTable"].ShowTableStyleFirstColumn = True 'Apply special style to last column ActiveSheet.ListObjects["myTable"].ShowTableStyleLastColumn = True End SubAdding or removing stripes
By default, tables have banded rows, but there are other options for this, such as removing row banding or adding column banding.
Sub ChangeStripes[] 'Apply column stripes ActiveSheet.ListObjects["myTable"].ShowTableStyleColumnStripes = True 'Remove row stripes ActiveSheet.ListObjects["myTable"].ShowTableStyleRowStripes = False End SubSet the default table style
The following macro sets the default table style.
Sub SetDefaultTableStyle[] 'Set default table style ActiveWorkbook.DefaultTableStyle = "TableStyleMedium2" End SubLooping through tables
The macros in this section loop through all the tables on the worksheet or workbook.
Loop through all tables on a worksheet
If we want to run a macro on every table of a worksheet, we must loop through the ListObjects collection.
Sub LoopThroughAllTablesWorksheet[] 'Create variables to hold the worksheet and the table Dim ws As Worksheet Dim tbl As ListObject Set ws = ActiveSheet 'Loop through each table in worksheet For Each tbl In ws.ListObjects 'Do something to the Table.... Next tbl End SubIn the code above, we have set the table to a variable, so we must refer to the table in the right way. In the section labeled Do something to the table, insert the action to be undertaken on each table, using tbl to reference the table.
For example, the following will change the table style of every table.
tbl.TableStyle = "TableStyleLight15"Loop through all tables in a workbook
Rather than looping through a single worksheet, as shown above, the macro below loops through every table on every worksheet.
Sub LoopThroughAllTablesWorkbook[] 'Create variables to hold the worksheet and the table Dim ws As Worksheet Dim tbl As ListObject 'Loop through each worksheet For Each ws In ActiveWorkbook.Worksheets 'Loop through each table in worksheet For Each tbl In ws.ListObjects 'Do something to the Table.... Next tbl Next ws End SubAs noted in the section above, we must refer to the table using its variable. For example, the following will display the totals row for every table.
tbl.ShowTotals = TrueAdding & removing rows and columns
The following macros add and remove rows, headers, and totals from a table.
Add columns into a table
The following macro adds a column to a table.
Sub AddColumnToTable[] 'Add column at the end ActiveSheet.ListObjects["myTable"].ListColumns.Add 'Add column at position 2 ActiveSheet.ListObjects["myTable"].ListColumns.Add Position:=2 End SubAdd rows to the bottom of a table
The next macro will add a row to the bottom of a table
Sub AddRowsToTable[] 'Add row at bottom ActiveSheet.ListObjects["myTable"].ListRows.Add 'Add row at the first row ActiveSheet.ListObjects["myTable"].ListRows.Add Position:=1 End SubDelete columns from a table
To delete a column, it is necessary to use either the column index number or the column header.
Sub DeleteColumnsFromTable[] 'Delete column 2 ActiveSheet.ListObjects["myTable"].ListColumns[2].Delete 'Delete a column by name ActiveSheet.ListObjects["myTable"].ListColumns["Feb"].Delete End SubDelete rows from a table
In the table structure, rows do not have names, and therefore can only be deleted by referring to the row number.
Sub DeleteRowsFromTable[] 'Delete row 2 ActiveSheet.ListObjects["myTable"].ListRows[2].Delete 'Delete multiple rows ActiveSheet.ListObjects["myTable"].Range.Rows["4:6"].Delete End SubAdd total row to a table
The total row at the bottom of a table can be used for calculations.
Sub AddTotalRowToTable[] 'Display total row with value in last column ActiveSheet.ListObjects["myTable"].ShowTotals = True 'Change the total for the "Total Column" to an average ActiveSheet.ListObjects["myTable"].ListColumns["TotalColumn"].TotalsCalculation = _ xlTotalsCalculationAverage 'Totals can be added by position, rather than name ActiveSheet.ListObjects["myTable"].ListColumns[2].TotalsCalculation = _ xlTotalsCalculationAverage End SubTypes of totals calculation
xlTotalsCalculationNone xlTotalsCalculationAverage xlTotalsCalculationCount xlTotalsCalculationCountNums xlTotalsCalculationMax xlTotalsCalculationMin xlTotalsCalculationSum xlTotalsCalculationStdDev xlTotalsCalculationVarTable header visability
Table headers can be turned on or off. The following will hide the headers.
Sub ChangeTableHeader[] ActiveSheet.ListObjects["myTable"].ShowHeaders = False End SubRemove auto filter
The auto filter can be hidden. Please note, the table header must be visible for this code to work.
Sub RemoveAutoFilter[] ActiveSheet.ListObjects["myTable"].ShowAutoFilterDropDown = False End SubI have a separate post about controlling auto filter settings check it out here. Most of that post applies to tables too.
Other range techniques
Other existing VBA techniques for managing ranges can also be applied to tables.
Using the union operator
To select multiple ranges, we can use VBAs union operator. Here is an example, it will select rows 4, 1, and 3.
Assign values from a variant array to a table row
To assign values to an entire row from a variant array, use code similar to the following:
Sub AssignValueToTableFromArray[] 'Assing values to array [for illustration] Dim myArray As Variant myArray = Range["A2:D2"] 'Assign values in array to the table ActiveSheet.ListObjects["myTable"].ListRows[2].Range.Value = myArray End SubReference parts of a table using the range object
Within VBA, a table can be referenced as if it were a standard range object.
Sub SelectTablePartsAsRange[] ActiveSheet.Range["myTable[Category]"].Select End SubCounting rows and columns
Often, it is useful to count the number of rows or columns. This is a good method to reference rows or columns which have been added.
Counting rows
To count the number of rows within the table, use the following macro.
Sub CountNumberOfRows[] Msgbox ActiveSheet.ListObjects["myTable"].ListRows.Count End SubCounting columns
The following macro will count the number of columns within the table.
Sub CountNumberOfColumns[] Msgbox ActiveSheet.ListObjects["myTable"].ListColumns.Count End SubUseful table techniques
The following are some other useful VBA codes for controlling tables.
Show the table data entry form
If a table starts at cell A1, there is a simple data entry form that can be displayed.
Sub ShowDataEntryForm[] 'Only works if Table starts at Cell A1 ActiveSheet.ShowDataForm End SubThe following screenshot shows the data form for the example table.
Check if a table exists
The following macro checks if a table already exists within a workbook. Change the tblNamevariable to adapt this to your requirements.
Sub CheckIfTableExists[] 'Create variables to hold the worksheet and the table Dim ws As Worksheet Dim tbl As ListObject Dim tblName As String Dim tblExists As Boolean tblName = "myTable" 'Loop through eac worksheet For Each ws In ActiveWorkbook.Worksheets 'Loop through each table in worksheet For Each tbl In ws.ListObjects If tbl.Name = tblName Then tblExists = True End If Next tbl Next ws If tblExists = True Then MsgBox "Table " & tblName & " exists." Else MsgBox "Table " & tblName & " does not exists." End If End SubFind out if a table has been selected, if so which
The following macros find the name of the selected table.
Method 1
As you will see in the comments Jon Peltier had an easy approach to this, which has now become my preferred approach.
Sub SimulateActiveTable[] Dim ActiveTable As ListObject On Error Resume Next Set ActiveTable = ActiveCell.ListObject On Error GoTo 0 'Confirm if a cell is in a Table If ActiveTable Is Nothing Then MsgBox "Select table and try again" Else MsgBox "The active cell is in a Table called: " & ActiveTable.Name End If End SubMethod 2
This option, which was my original method, loops through each table on the worksheet and checks if they intersect with the active cell.
Sub SimulateActiveTable_Method2[] Dim ActiveTable As ListObject Dim tbl As ListObject 'Loop through each table, check if table intersects with active cell For Each tbl In ActiveSheet.ListObjects If Not Intersect[ActiveCell, tbl.Range] Is Nothing Then Set ActiveTable = tbl MsgBox "The active cell is in a Table called: " & ActiveTable.Name End If Next tbl 'If no intersection then no tabl selected If ActiveTable Is Nothing Then MsgBox "Select an Excel table and try again" End If End SubConclusion
Wow!That was a lot of code examples.
There are over 30 VBA macros above, and even this does not cover everything, but hopefully covers 99% of your requirements. For your remaining requirements, you could try Microsofts VBA object reference library [//docs.microsoft.com/en-us/office/vba/api/Excel.ListObject]
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.Email Address *
First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. Well respect your privacy and you can unsubscribe at any time.
Dont forget:
If youve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
Im guessing the examples in this post didnt exactly meet your situation. We all use Excel differently, so its impossible to write a post that will meet everybodys needs. By taking the time to understand the techniques and principles in this post [and elsewhere on this site] you should be able to adapt it to your needs.
But, if youre still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the Excel Ninja in your office. Its amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure its clear and concise. List all the things youve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Dont go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: