This blog post will be based on working with tables via VBA. They present some unique issues, which has to do with how their structured in the object model. Good or bad, it’s what we have. These are based on my trial and errors, so hopefully this will help somebody out there. That being said I consider this an intermediate level post.

If you’re not up to speed with tables, take a look at these:

http://excelandaccess.wordpress.com/2013/01/07/why-you-should-use-structured-references-in-tables/

When looking for information about tables, or adding/deleting data, it can be tricky. Here are some pieces of information to keep in mind, which isn’t very obvious, but are cornerstones in working with tables in VBA:

  • Deleting the DataBodyRange (data portion of a table) will set it to Nothing *
  • If table parts (i.e. header, totals row) aren’t showing, attempting access will result in an error
  • Accessing the straight Range of a table includes all visible parts (i.e.

[#All])

  • Tables are ListObjects *
  • If you’ve worked with tables in VBA, you may have noticed that when deleting a table’s entire data body, you are still left with a single, blank row. This is because you must have at least one row of data for the table to still be “alive”. No data rows, no table. You can hide the header and totals row, but you can’t get rid of the body. This presents some interesting coding when working with tables, and can be problematic, aka headache-inducing.

    * Check the links at the bottom of the post for additional information on these topics.

    Here are some VBA examples for doing various things with tables in VBA. The file will be available (link at bottom of post) for download.

    Common variables used throughout

    [sourcecode language=”text”] Public WS As Worksheet

    Public loSet As ListObject
    Public loGet As ListObject
    Public loTest As ListObject

    Public rCols As Range
    Public rRows As Range
    Public rBody As Range
    Public rData As Range
    Public rHeader As Range
    Public rStart As Range

    Public iCol As Long
    Public iRow As Long
    Public iStep As Long
    Public iLastRow As Long
    Public iRowCnt As Long
    Public iColCnt As Long

    Public sMsg As String

    Public Const sSheetName As String = “Sheet1”
    Public Const sTableName As String = “Table1”
    Public Const sTableName2 As String = “Table2”
    Public Const NL As String = vbNewLine
    Public Const DNL As String = vbNewLine & vbNewLine[/sourcecode]

    As you can see I used some constants for testing in this specific workbook. Change these variables for testing in your environment. I tried to keep the naming convention simple.

    Getting information from your tables

    When working with tables via VBA it’s important to know how to look at them, and thus where to go for information. These are some examples of how to get dimension(s) information from your tables.

    Listing columns

    This will step through a table and list the column headers in a message box, as well as the Immediate window in the Visual Basic Editor (Ctrl + G to show it).

    [sourcecode language=”vb”] Sub Table_ListColumns()

    On Error Resume Next
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loTest = WS.ListObjects(sTableName)
    On Error GoTo 0

    If WS Is Nothing Or loTest Is Nothing Then
    MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
    Exit Sub
    End If

    sMsg = vbNullString
    For iStep = 1 To loTest.ListColumns.Count
    Debug.Print loTest.ListColumns.Item(iStep).Name
    sMsg = sMsg & NL & iStep & “) ” & loTest.ListColumns(iStep)
    Next iStep
    sMsg = “‘” & loTest.Name & “‘ column headers:” & NL & sMsg

    MsgBox sMsg, vbOKOnly, “COLUMN HEADERS”

    End Sub[/sourcecode]

    Column count

    This will not loop through the table, but give a straight count of columns, along with which/where the start/end columns are.

    [sourcecode language=”vb”] Sub Table_CountColumns()

    On Error Resume Next
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loTest = WS.ListObjects(sTableName)
    On Error GoTo 0

    If WS Is Nothing Or loTest Is Nothing Then
    MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
    Exit Sub
    End If

    sMsg = “There are ” & loTest.ListColumns.Count & ” columns in ‘” & sTableName & “‘.”
    sMsg = sMsg & DNL & “Starting on column ” & loTest.ListColumns(1).Range.Column & ” (” & loTest.ListColumns(1) & “)”
    sMsg = sMsg & NL & “Ending on column ” & loTest.ListColumns(loTest.ListColumns.Count).Range.Column & ” (” & loTest.ListColumns(loTest.ListColumns.Count) & “)”

    MsgBox sMsg, vbInformation, UCase(sTableName)

    End Sub[/sourcecode]

    Row count

    Again, no loop, just find out how many rows are in the table and where they are.

    [sourcecode language=”vb”] Sub Table_CountRows()

    On Error Resume Next
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loTest = WS.ListObjects(sTableName)
    On Error GoTo 0

    If WS Is Nothing Or loTest Is Nothing Then
    MsgBox “There was an error setting the variables.”, vbInformation, “ERROR!”
    Exit Sub
    End If

    sMsg = “There are ” & loTest.ListRows.Count & ” rows in ‘” & sTableName & “‘.”
    sMsg = sMsg & DNL & “Starting on row ” & loTest.ListRows(1).Range.Row
    sMsg = sMsg & NL & “Ending on row ” & loTest.ListRows(loTest.ListRows.Count).Range.Row

    MsgBox sMsg, vbInformation, UCase(sTableName)

    End Sub[/sourcecode]

    Resizing Tables

    Resize table based on original

    This will basically clear out the manual data and leave the formulas. It gets a row count and uses that to insert rows to its original size. Please note these focus on resizing rows programmatically, not columns, which can be trickier.

    [sourcecode language=”vb”] Sub ResizeTableRows()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    ‘/// Ensure the table has a data range
    If loSet.DataBodyRange Is Nothing Then
    MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
    Exit Sub
    End If

    ‘/// Get the number of rows in the table
    iRowCnt = loSet.ListRows.Count

    ‘/// Delete destination table data
    If Not loSet.DataBodyRange Is Nothing Then
    loSet.DataBodyRange.Delete
    End If

    ‘/// Resize to source table size
    For iStep = 1 To iRowCnt
    loSet.ListRows.Add iStep
    Next iStep

    ‘///////////////////////////////////////////////////////////////////////////
    ‘/// At this point the table is restructured, and the formulas which were
    ‘/// there are re-populated
    ‘///////////////////////////////////////////////////////////////////////////

    End Sub[/sourcecode]

    Resize table based on another tables size (in rows)

    This utilizes two tables. The second table is only used for its row size and makes the first table the same size. It deletes the DataBodyRange as well, so all manual data will be erased, leaving only formulas.

    [sourcecode language=”vb”] Sub ResizeTableRowsBasedOnAnother()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)

    ‘/// Set two tables, one to set (destination)
    ‘/// and one to resize from (source)
    Set loSet = WS.ListObjects(sTableName)
    Set loGet = WS.ListObjects(sTableName2)

    ‘/// Delete destination table data
    If Not loSet.DataBodyRange Is Nothing Then
    loSet.DataBodyRange.Delete
    End If

    ‘/// Ensure source table has data range
    If loGet.DataBodyRange Is Nothing Then
    MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
    Exit Sub
    End If

    ‘/// Resize to source table size
    For iStep = 1 To loGet.ListRows.Count
    loSet.ListRows.Add iStep
    Next iStep

    ‘///////////////////////////////////////////////////////////////////////////
    ‘/// At this point the table is restructured, and the formulas which were
    ‘/// there are re-populated
    ‘///////////////////////////////////////////////////////////////////////////

    End Sub[/sourcecode]

    Resize table columns

    This example will add a single column. While I am very hesitant to code something like this, here is an example. While it’s easier to add rows, adding columns present some unique problems, which include:

    • Where should the columns be inserted?
    • Without proper absolute structured formula referencing, this could throw off formulas
    • Ideally no data should be to the right of the table, so an error won’t be thrown.

    [sourcecode language=”vb”] Sub ResizeTableCols()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    ‘/// Ensure the table has a data range
    If loSet.DataBodyRange Is Nothing Then
    MsgBox “The source table has no data stucture.”, vbExclamation, “ERROR!”
    Exit Sub
    End If

    ‘/// Get the number of columns in the table, add one
    iColCnt = loSet.ListColumns.Count + 1

    ‘/// Delete destination table data
    If Not loSet.DataBodyRange Is Nothing Then
    loSet.DataBodyRange.Delete
    End If

    ‘/// Resize to source table size
    On Error Resume Next
    For iStep = 1 To iColCnt
    If loSet.ListColumns(iStep) Is Nothing Then
    Err.Clear
    loSet.ListColumns.Add
    If Err.Number > 0 Then
    ‘something prevented the column from being added
    Exit For
    End If
    End If
    Next iStep
    On Error GoTo 0

    ‘///////////////////////////////////////////////////////////////////////////
    ‘/// At this point the table is restructured, and the formulas which were
    ‘/// there are re-populated
    ‘///////////////////////////////////////////////////////////////////////////

    End Sub[/sourcecode]

    Select table parts

    Table ranges

    It’s always best to work with actual table range referernces. While you can access the range object of the underlying worksheet, it gets tough. This shows an example.

    [sourcecode language=”vb”] Sub ListTableParts()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    If loSet.DataBodyRange Is Nothing Then
    If MsgBox(“Data range has been deleted. Create now?”, vbYesNo, “ERROR!”) = vbYes Then
    loSet.ListRows.Add 1
    End If
    End If

    ‘/// Using standard table referencing objects
    sMsg = “Standard table referencing” & DNL
    If Not loSet.DataBodyRange Is Nothing Then
    sMsg = sMsg & “Data range: ” & loSet.DataBodyRange.Address(0, 0) & NL
    End If
    If Not loSet.HeaderRowRange Is Nothing Then
    sMsg = sMsg & “Header range: ” & loSet.HeaderRowRange.Address(0, 0) & NL
    Else
    sMsg = sMsg & “Header range: (none set)” & NL
    End If
    If Not loSet.TotalsRowRange Is Nothing Then
    sMsg = sMsg & “Totals range: ” & loSet.TotalsRowRange.Address(0, 0) & NL
    Else
    sMsg = sMsg & “Totals range: (none set)”
    End If

    ‘/// Using range referencing objects
    sMsg = sMsg & DNL & “Range referencing” & NL
    If Not loSet.DataBodyRange Is Nothing Then
    sMsg = sMsg & “Data range: ” & loSet.Range(1, 1).Address(0, 0) & “:” & loSet.Range(loSet.Range.Rows.Count, loSet.Range.Columns.Count).Address(0, 0) & NL
    End If

    MsgBox sMsg, vbInformation, “INFO”

    End Sub[/sourcecode]

    Select table Header

    This selects just the header range of the table, if it is showing. If it’s not showing the user gets the option to show it at that time.

    [sourcecode language=”vb”] Sub SelectHeader()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    If Not loSet.HeaderRowRange Is Nothing Then
    loSet.HeaderRowRange.Select
    Else
    If MsgBox(“Header row isn’t showing. Show now?”, vbYesNo, “ERROR!”) = vbYes Then
    loSet.ShowHeaders = True
    End If
    End If

    End Sub[/sourcecode]

    Select table data

    This selects just the data range of the table, if it is not deleted. If it’s not there the user gets the option to add it at that time.

    NOTE: Adding a data body (DataBodyRange) to a table is as simple as inserting a row. This is quite a useful trick when working with tables via VBA.

    [sourcecode language=”vb”] Sub SelectData()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    If Not loSet.DataBodyRange Is Nothing Then
    loSet.DataBodyRange.Select
    Else
    If MsgBox(“Data range has been deleted. Create now?”, vbYesNo, “ERROR!”) = vbYes Then
    loSet.ListRows.Add 1
    End If
    End If

    End Sub[/sourcecode]

    Select table Totals row

    This selects just the totals row range of the table, if it is showing. If it’s not showing the user gets the option to show it at that time.

    [sourcecode language=”vb”] Sub SelectTotalRow()

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    If Not loSet.TotalsRowRange Is Nothing Then
    loSet.TotalsRowRange.Select
    Else
    If MsgBox(“Totals row isn’t showing. Show now?”, vbYesNo, “ERROR!”) = vbYes Then
    loSet.ShowTotals = True
    End If
    End If

    End Sub[/sourcecode]

    Show table parts

    This routine has a Boolean (True/False) variable passed to it to turn on or off all table parts (non-formatting)

    [sourcecode language=”vb”] Sub ShowTableParts(Optional ByVal bState As Boolean = True)

    ‘/// Set misc variables
    Set WS = ThisWorkbook.Worksheets(sSheetName)
    Set loSet = WS.ListObjects(sTableName)

    loSet.ShowTotals = bState
    loSet.ShowHeaders = bState
    If bState = True Then loSet.ShowAutoFilter = bState

    End Sub [/sourcecode]

    Note that the ShowAutoFilter portion only runs if a True variable is passed (turning properties on). This is because if you turn off the header row, there’s nothing to filter on, so there’s no need to worry about it.

    Summary

    The important parts of the object model to look at with respect to tables are:

    • ListObject
    • DataBodyRange
    • HeaderRowRange
    • TotalsRowRange
    • ListRows
    • ListColumns

    There are other things you can do with tables via VBA, this certainly isn’t the complete guide. Some of those things include:

    • Work with slicers *
    • Unlist it (convert to standard range)
    • Set style/formatting options

    Additional information

    Jan Karel Pieterse: http://www.jkp-ads.com/articles/Excel2007Tables.asp

    Colin Legg: http://colinlegg.wordpress.com/2012/09/01/clearing-excel-tables/

    Sample file for download: tblExample

    File tested and works in Excel 2010 and 2013 without issue.

    A special thanks to Chris “Smitty” Smith, Christopher T. Fennell and Colin Legg, equally for the direction, support and inspiration for this blog post. Thank you.