Working with Tables in Excel (VBA)

Introduction

In Working with Tables in Excel I promised to add a page about working with those tables in VBA too. Well, here you go.

It's a ListObject!

On the VBA side there seems to be nothing new about Tables. They are addressed as ListObjects, a collection that was introduced with Excel 2003. But there are significant changes to this part of the object model and I am only going to touch on the basic parts here.

Creating a table

Converting a range to a table is simple enough:

 Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = _
        "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

Table formatting is determined by TableStyles. A collection of objects which are a member of the Workbook object. This gives rise to some oddities. You can change the formatting of a tableStyle, e.g. like this:

Sub ChangeTableStyles()
    ActiveWorkbook.TableStyles(2).TableStyleElements(xlWholeTable) _
        .Borders(xlEdgeBottom).LineStyle = xlDash
End Sub

This changes the linestyle of the bottom of your table. But hold your horses! If you have any other workbook open, all tables with the same tablestyle now use your changed style! But if you save your file, close Excel and open Excel again with the file, the changes are gone. This is because you've just changed a built-in tablestyle. If you ask me, I find it strange that the Workbook is a tablestyles' parent, whereas built-in table styles behave as if being bound to the Application object.

If you want full control over your table style, you'd better duplicate a built-in style and modify and apply that style to your table.

Listing the tables

Let's start with finding all tables on the active worksheet:

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
    Next
End Sub

Selecting parts of tables

You might need to work with specific parts of a table. Here is a couple of examples on how to achieve that.

Sub SelectingPartOfTable()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '1: with the listobject
    With oSh.ListObjects("Table1")
        MsgBox .Name
        'Select entire table
        .Range.Select
        'Select just the data of the entire table
        .DataBodyRange.Select
        'Select third column
        .ListColumns(3).Range.Select
        'Select only data of first column
        .ListColumns(1).DataBodyRange.Select
        'Select just row 4 (header row doesn't count!)
        .ListRows(4).Range.Select
    End With
   
    '2: with the range object
    'select an entire column (data only)
    oSh.Range("Table1[Column2]").Select
    'select an entire column (data plus header)
    oSh.Range("Table1[[#All],[Column1]]").Select
    'select entire data section of table
    oSh.Range("Table1").Select
    'select entire table
    oSh.Range("Table1[#All]").Select
    'Select one row in table
    oSh.Range("A5:F5").Select
End Sub

As you may have spotted, current Excel versions handle tables like they are range names. Well, that is exactly what is going on. After inserting a table, a range name is defined automatically. These range names are special though. Excel controls them entirely. You cannot delete them and they get renamed automatically when you change a table's name. Remove a table (convert back to range) and the defined name is removed as well.

Inserting rows and columns

Another part in which lists already had most of the functionality. Just a few new things have been added, like the "AlwaysInsert" argument to the ListRows.Add method:

Sub TableInsertingExamples()
'insert at specific position
    Selection.ListObject.ListColumns.Add Position:=4
'insert right
    Selection.ListObject.ListColumns.Add
'insert above
    Selection.ListObject.ListRows.Add (11)
'insert below
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
End Sub

If you need to do something with a newly inserted row, you can set an object variable to the new row:

     Dim oNewRow As ListRow
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)

If you then want to write something in the first cell of the new row you can use:

oNewRow.Range.Cells(1,1).Value = "Value For New cell"

Adding a comment to a table

Adding a comment to a table through the UI is a challenge, because you have to go to the Name Manager to do that. In VBA the syntax is:

Sub AddComment2Table()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'add a comment to the table (shows as a comment to
    'the rangename that a table is associated with automatically)
    'Note that such a range name cannot be deleted!!
    'The range name is removed as soon as the table is converted to a range
    oSh.ListObjects("Table1").Comment = "This is a table's comment"
End Sub

Convert a table back to a normal range

That is simple:

Sub RemoveTableStyle()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'remove table or list style
    oSh.ListObjects("Table1").Unlist
End Sub

Special stuff: Sorting and filtering

With tables, we get a whole new set of filtering and sorting options. I'm only showing a tiny bit here, a Sort on cell color (orangish) and a filter on the font color.

Sub SortingAndFiltering()
'NoGo in 2003
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")

        .Sort.SortFields.Clear
        .Sort.SortFields.Add( _
                Range("Table1[[#All],[Column2]]"), xlSortOnCellColor, xlAscending, , _
                xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, _
        Criteria1:=RGB(156, 0, 6), Operator:=xlFilterFontColor
End Sub

Accessing the formatting of a cell inside a table

You may wonder why this subject is there, why not simply ask for the cell.Interior.ThemeColor if you need the ThemeColor of a cell in a table? Well, because the cell formatting is completely prescribed by the settings of your table and the table style that  has been selected. So in order to get at a formatting element of a cell in your table you need to:

  • Find out where in your table the cell is located (on header row, on first column, in the bulk of the table
  • Determine the table settings: does it have row striping turned on, does it have a specially formatted first column, ...
  • Based on these pieces of information, one can extract the appropriate TableStyleElement from the table style and read its properties.

The function shown here returns the TableStyleElement belonging to a cell oCell inside a table object called oLo:

Function GetStyleElementFromTableCell(oCell As Range, oLo As ListObject) As TableStyleElement
'-------------------------------------------------------------------------
' Procedure : GetStyleElementFromTableCell
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 2-6-2009
' Purpose   : Function to return the proper style element from a cell inside a table
'-------------------------------------------------------------------------
    Dim lRow As Long
    Dim lCol As Long
    'Determine on what row we are inside the table
    lRow = oCell.Row - oLo.DataBodyRange.Cells(1, 1).Row
    lCol = oCell.Column - oLo.DataBodyRange.Cells(1, 1).Column

    With oLo
        If lRow < 0 And .ShowHeaders Then
            'on first row and has header
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlHeaderRow)
        ElseIf .ShowTableStyleFirstColumn And lCol = 0 Then
            'On first column and has first column style
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlFirstColumn)
        ElseIf .ShowTableStyleLastColumn And lCol = oLo.Range.Columns.Count - 1 Then
            'On last column and has last col style
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlLastColumn)
        ElseIf lRow = .DataBodyRange.Rows.Count And .ShowTotals Then
            'On last row and has total row
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlTotalRow)
        Else
            If .ShowTableStyleColumnStripes And Not .ShowTableStyleRowStripes Then
                'in table, has column stripes
                If lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            ElseIf .ShowTableStyleRowStripes And Not .ShowTableStyleColumnStripes Then
                'in table, has column stripes
                If lRow Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            ElseIf .ShowTableStyleColumnStripes And .ShowTableStyleRowStripes Then
                If lRow Mod 2 = 0 And lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                ElseIf lRow Mod 2 <> 0 And lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
                ElseIf lRow Mod 2 = 0 And lCol Mod 2 <> 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            End If
        End If
    End With

End Function

You could use this function like this:

Sub test()
    Dim oLo As ListObject
    Dim oTSt As TableStyleElement
    Set oLo = ActiveSheet.ListObjects(1)
    Set oTSt = GetStyleElementFromTableCell(ActiveCell, oLo)
    With ActiveCell.Offset(, 8)
        .Interior.ThemeColor = oTSt.Interior.ThemeColor
        .Interior.TintAndShade = oTSt.Interior.TintAndShade
    End With
End Sub

Removing formating from an Excel Table

Suppose you have just converted a range to a table, but the range had some formatting set up such as background fills and borders. Tables allow you to format things like that automatically, but now your preexisting formatting messes up the table formatting. One way to overcome this is by changing the style of the cells (see this article) in the table back to the Normal style. This however removes your number formats too. The little macro below fixes that by first making a copy of the normal style, setting its Number checkbox to false and then applying the new style without number format to the table. Finally it applies the tablestyle and deletes the temporary style:

Sub RemoveFormattingOfTable()
    Dim oStNormalNoNum As Style
    On Error Resume Next
    Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
    On Error GoTo 0
    If oStNormalNoNum Is Nothing Then
        ActiveWorkbook.Styles.Add "NormalNoNum"
        Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
        oStNormalNoNum.IncludeNumber = False
    End If
    With ActiveSheet.ListObjects(1)
        .Range.Style = "NormalNoNum"
        'Now apply tablestyle:
        .TableStyle = "TableStyleLight1"
    End With
    ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

Note that the function shown above does not take into account that you can set the width of the stripes, both vertically and horizontally.

Wrap Up

Of course there is more to learn and know about tables and lists. A good way to come acquainted with the VBA behind them is by recording macro's while fooling around with them. Luckily Microsoft did include the table object if it comes to recording your actions, unlike the omission on the charting side...


Comments

Showing last 8 comments of 857 in total (Show All Comments):

 


Comment by: João Figueiredo (30-5-2020 11:53:00) deeplink to this comment

Sorry:) i didin´t know.


Comment by: Jan Karel Pieterse (30-5-2020 11:54:00) deeplink to this comment

Hi Joao,

Filling the visible cells of a table column with a value is simple:

lo.ListColumns("SentColumnName").DataBodyRange.SpecialCells(xlCellTypeVisible).Value = "Sent"


Comment by: Paul Deaton (28-3-2021 00:12:00) deeplink to this comment

Wonderful website!! Thanks for your MANY contributions.

I am using Excel 2016 on Windows 10 Pro.

When using the *.ListRows.Add method, I find the results to be oblivious to the AlwaysInsert parameter. Even when I specify AlwaysInsert:=False, I get exactly the same result as the default True. If there are 3 empty rows below the Excel table followed by a 4th row populated with data, after the .Add, there are STILL 3 empty rows.

Searching the web, I see a few other folks with a similar complaint, but no meaningful responses. Has anyone verified that AlwaysInsert:=False works as explained in MS documentation?


Comment by: Jan Karel Pieterse (29-3-2021 14:32:00) deeplink to this comment

Hi Paul,

Thanks for the comment!

The behaviour with AlwaysInsert in all versions appears to be: If Position is not specified AlwaysInsert works as anticipated, True by default or False if specified as False. However if Position is specified AlwaysInsert works as True even if specified as False. The documentation would seem to be incomplete in this respect.


Comment by: Tom (27-5-2021 01:36:00) deeplink to this comment

Thanks for the useful information on this page, it helped us to improve our Excel macro's performance a lot.


Comment by: Jan Karel Pieterse (27-5-2021 10:23:00) deeplink to this comment

Hi Tom,

You're welcome!


Comment by: Michel Saulnier (23-7-2021 13:35:00) deeplink to this comment

Thank you for this VBA tables info, not a lot is available. Here is include my code for adding a row by positioning yourself anywhere in the table even if table is not necessarily starting a the first row:


Sub Add_Copy_Paste_Row_Table()
'
' ADD COPY PASTE ROW above select cell IN TABLE EXCEL
' When table start at any row
' Found a way to link row(cell) in sheet to row(cell) in table
'
    Dim Row_ID As Integer
    Dim Table_Row As Integer
    Dim Table_ID As String
    Dim Real_Row As Integer
    Dim Row_Add As Integer

    ' EXCEL row number
    Row_ID = ActiveCell.Row
    Table_ID = ActiveCell.ListObject.Name
    ' count number of row in able including header
    Table_Row = ActiveSheet.ListObjects(Table_ID).Range.Rows.Count
    ' Select last row
    ActiveSheet.ListObjects(Table_ID).ListRows(Table_Row - 1).Range.Select
    ' EXCEL last table row number
    Real_Row = ActiveCell.Row
    ' How to make the magic happen
    Row_Add = Real_Row - Table_Row
    ' Turning off filter in order to avoid any error
    ActiveSheet.ListObjects(Table_ID).Range.AutoFilter
    ' the code :
    ' Add row above selected row
    Selection.ListObject.ListRows.Add (Row_ID - Row_Add - 1)
    ActiveSheet.ListObjects(Table_ID).ListRows(Row_ID - Row_Add).Range.Select
    Selection.Copy
    ActiveSheet.ListObjects(Table_ID).ListRows(Row_ID - Row_Add - 1).Range.Select
    ActiveSheet.Paste
' Turning back on filter
    ActiveSheet.ListObjects(Table_ID).Range.AutoFilter
End Sub

Hope this can help someone or if you have a more efficient way to do it

Michel S


Comment by: Jan Karel Pieterse (23-7-2021 13:43:00) deeplink to this comment

Hi Michael,

Thanks for posting!
Indeed that can be shortened, for example:

With ActiveCell.ListObject
    .ListRows.Add ActiveCell.Row - .Range.Cells(1, 1).Row
End With

I omitted the turning on and off of the filter though.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].