[sourcecode language=”vb”] Sub ShowTableParts(ByVal ShowParts As Boolean)
Dim loSet As ListObject
‘/// Set table
Set loSet = ActiveSheet.ListObjects(“Table1”)
‘/// If data body range has been deleted, re-instate it
If loSet.DataBodyRange Is Nothing Then
loSet.ListRows.Add
End If
‘/// We must use this statement in case the table ranges
‘/// cannot be expanded, an error will be thrown
On Error Resume Next
Err.Clear
‘/// Show headers
loSet.ShowHeaders = ShowParts
If Err.Number <> 0 Then Debug.Print “Headers of ‘” & loSet.Name & “‘ can’t be moved”
Err.Clear
‘/// Show totals
loSet.ShowTotals = ShowParts
If Err.Number <> 0 Then Debug.Print “Totals row of ‘” & loSet.Name & “‘ can’t be moved”
Err.Clear

On Error GoTo 0
End Sub[/sourcecode]

 

Excel VBA Code Routine

A routine to call this with would look like this…

[sourcecode language=”vb”] Sub ShowThem()
Call ShowTableParts(False)
End Sub

Sub HideThem()
Call ShowTableParts(True)
End Sub[/sourcecode]

You’ll notice there are “Debug.Print” commands in the code, which print to your Immediate window (Ctrl + G).

[sourcecode language=”text”] Totals row of ‘Table1’ can’t be moved[/sourcecode]

This is because if that table part can’t be shown, an error will be raised.

The Reason

This is due to the way tables are designed and how these table object parts show/hide. The VBA objects become Nothing when visibility is toggled off, so it’s an easy test there. But what actually happens on a worksheet is basically an Insert command for that range of cells, which spans only the width of the table. This presents a problem if you have data ranges below the table which are a different dimension or configuration. Most notably are 1) other tables, 2) merged cells, 3) an XML mapping, 4) query table, or 5) a PivotTable.

In the picture below, there is a set of merged cells directly underneath the table. Currently it does not have its totals row showing.

If you then click on the totals row button, located on the Table Tools | Design contextual tab (meaning you must have any cell in the table selected):

…this is the error you’ll see raised:

That is because of the merged cells, and Excel doesn’t want to make assumptions that, if by inserting cells in column B only (the total span of the table columns) it would insert rows going down, it’s ok to break your merged cells.

If, however, you choose to insert a new row into your table (put cursor in the bottom-right cell of the table data body, or right-click any data body cell in the table and choose Insert | Table Rows Above/Below)…

…in the same example (with merged cells directly below your table range), you will get the option as seen below.

By clicking OK to this message your merged cells will become unmerged and column B will have a cell inserted at B5, thus un-merging your cells below.

-------

Getting the Range Addresses in an Excel Table

If you pass the table object, it is fairly easy to get the ranges associated with that table object. Here are three functions to get the address of the three table objects if they’re showing (i.e. not Nothing):

[sourcecode language=”vb”] Function TABLEDATARANGE(ByVal loTest As ListObject) As String
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Returns the range address of the specified table’s data body range.
‘———————————————————————————————
If Not loTest.DataBodyRange Is Nothing Then
TABLEDATARANGE = loTest.DataBodyRange.Address
End If
End Function

Function TABLEHEADERRANGE(ByVal loTest As ListObject) As String
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Returns the range address of the specified table’s header row range.
‘———————————————————————————————
If Not loTest.HeaderRowRange Is Nothing Then
TABLEHEADERRANGE = loTest.HeaderRowRange.Address
End If
End Function

Function TABLETOTALSRANGE(ByVal loTest As ListObject) As String
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Returns the range address of the specified table’s totals row range.
‘———————————————————————————————
If Not loTest.TotalsRowRange Is Nothing Then
TABLETOTALSRANGE = loTest.TotalsRowRange.Address
End If
End Function[/sourcecode]

 

 

---

 

Test Table Object Visibility

You can also see if a table object is visible or not (i.e. not Nothing). This can be used as a worksheet function with a cell reference passed as a range argument:

[sourcecode language=”vb”] Function TABLEHEADERVISIBLE(ByVal TableCell As Range) As Boolean
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Looks at a cell to see if it is part of a table and if the header is
‘/// is visible.
‘———————————————————————————————
Dim loTest As ListObject

‘/// Set temporary table
On Error Resume Next
Set loTest = TableCell.ListObject
On Error GoTo 0

‘/// Check if it is a table (ListObject)
If loTest Is Nothing Then
TABLEHEADERVISIBLE = CVErr(xlErrNA)
Exit Function
End If

‘/// Return visibility status of table header
TABLEHEADERVISIBLE = loTest.ShowHeaders

End Function

Function TABLETOTALSROWVISIBLE(ByVal TableCell As Range) As Boolean
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Looks at a cell to see if it is part of a table and if the totals row is
‘/// is visible.
‘———————————————————————————————
Dim loTest As ListObject

‘/// Set temporary table
On Error Resume Next
Set loTest = TableCell.ListObject
On Error GoTo 0

‘/// Check if it is a table (ListObject)
If loTest Is Nothing Then
TABLETOTALSROWVISIBLE = CVErr(xlErrNA)
Exit Function
End If

‘/// Return visibility status of table totalsrow
TABLETOTALSROWVISIBLE = loTest.ShowTotals

End Function[/sourcecode]

 

Example – Get Excel Table Addresses

 

When entered into a cell it will look fairly silly, for example:

=TABLEHEADERVISIBLE(Table1

[[#Headers],[a]])

You can obviously tell it is referring to a table range because of the structured table reference. This was accomplished by clicking on the header of the table when entering the formula. If you type in the range manually, it may not be so obvious.

=TABLEHEADERVISIBLE(B3)

The above formula will yield the same results (same sample table as shown before).

If you have a formula which references a table object, for example the header range, and you uncheck Header Row from the Table Style Options group, you will raise an error.

You’ll see this no matter how you reference the header, either structured table references or standard cell referencing (or even R1C1 referencing style).

 

 

Getting the Range Addresses from a Cell Reference

These functions are almost identical to the range functions above, with the only difference that you must pass a cell reference instead of a table (ListObject) reference.

[sourcecode language=”vb”] Function TABLEDATARANGE2(ByVal TableCell As Range) As String
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Looks at a cell to see if it is part of a table and returns its data range
‘/// address.
‘———————————————————————————————
Dim loTest As ListObject

‘/// Set temporary table
On Error Resume Next
Set loTest = TableCell.ListObject
On Error GoTo 0

‘/// Check if it is a table (ListObject)
If loTest Is Nothing Then
TABLEDATARANGE2 = CVErr(xlErrNA)
Exit Function
End If

Code Continues:

‘/// If data range is there, return its address
If Not loTest.DataBodyRange Is Nothing Then
TABLEDATARANGE2 = loTest.DataBodyRange.Address
Else
TABLEDATARANGE2 = CVErr(xlErrNA)
End If

End Function

Function TABLEHEADERRANGE2(ByVal TableCell As Range) As String
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Looks at a cell to see if it is part of a table and returns its header
‘/// address.
‘———————————————————————————————
Dim loTest As ListObject

‘/// Set temporary table
On Error Resume Next
Set loTest = TableCell.ListObject
On Error GoTo 0

‘/// Check if it is a table (ListObject)
If loTest Is Nothing Then
TABLEHEADERRANGE2 = CVErr(xlErrNA)
Exit Function
End If

‘/// If data headers are there, return its address
If Not loTest.HeaderRowRange Is Nothing Then
TABLEHEADERRANGE2 = loTest.HeaderRowRange.Address
Else
TABLEHEADERRANGE2 = CVErr(xlErrNA)
End If

End Function

Function TABLETOTALSRANGE2(ByVal TableCell As Range) As String
‘———————————————————————————————
‘/// Created on: 13-Jan-2013
‘/// Created by: Microsoft MVP
‘/// Purpose: Looks at a cell to see if it is part of a table and returns its totals row
‘/// address.
‘———————————————————————————————
Dim loTest As ListObject

‘/// Set temporary table
On Error Resume Next
Set loTest = TableCell.ListObject
On Error GoTo 0

‘/// Check if it is a table (ListObject)
If loTest Is Nothing Then
TABLETOTALSRANGE2 = CVErr(xlErrNA)
Exit Function
End If

‘/// If totals row is there, return its address
If Not loTest.TotalsRowRange Is Nothing Then
TABLETOTALSRANGE2 = loTest.TotalsRowRange.Address
Else
TABLETOTALSRANGE2 = CVErr(xlErrNA)
End If

End Function[/sourcecode]

In all three of these functions, if there is no table, the function will return the #VALUE! error. Likewise, if that object part of the table is non-existent, it will again return the #VALUE! error.  Below are examples of all three functions in action. See how it works.

Conclusion – Get Excel Table Addresses

While there isn’t any good built-in way to get table object states or ranges, VBA offers alternatives.  For arguments sake, you could use this formula to return the header range:

=ADDRESS(ROW(Table1[#Headers]),COLUMN(Table1[#Headers]))

Although this is only good for a one-column wide table, which generally isn’t the case.  To get the entire header range you would need something like this:

=IF(COUNTA(Table1[#Headers])=1,ADDRESS(ROW(Table1[#Headers]),COLUMN(Table1[#Headers])),ADDRESS(ROW(Table1[#Headers]),COLUMN(Table1[#Headers]))&”:”&ADDRESS(ROW(Table1[#Headers]),COLUMN(OFFSET(Table1[#Headers],0,COUNTA(Table1[#Headers])-1))))

 

=tblStaff[#Headers]

 

I hope this has been helpful and this information comes in handy.