This post may contain affiliate links. Please read my disclosure for more info.

VBA code to control and manage Excel Tables

VBA Code Snippets

Tables are one of the most powerful features of Excel.  Being able to automate them using VBA provides a way to make Tables even more powerful.

Creating and converting Tables

This first set of macros is about creating and resizing macros.

Convert selection to Excel Table

This macro will create a Table based on the currently selected region and name it “myTable”

Sub ConvertRangeToTable()

ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, _
    Source:=Selection.CurrentRegion, _
    xlListObjectHasHeaders:=xlYes _
    ).Name = "myTable"

End Sub

Convert Excel Table back to Range

This macro will convert a Table back to a normal range.

Sub ConvertTableToRange()

ActiveSheet.ListObjects("myTable").Unlist

End Sub

Resize the range of the table

To following macro will resize a Table to Cell A1 – J100

Sub ResizeTableRange()

ActiveSheet.ListObjects("myTable").Resize Range("$A$1:$J$100")

End Sub

Table Styles

There are many formatting options, which can be used with Tables.  The most common of which are shown below.

Change Table Style

Change the style of a Table to an existing pre-defined style.

Sub ChangeTableStyle()

ActiveSheet.ListObjects("myTable").TableStyle = "TableStyleLight15"

End Sub

To apply different Table styles, use the macro recorder to find the name of a style.

Apply style to 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 Sub

Some alternative codes you could use are:

Remove special style from first column

ActiveSheet.ListObjects("myTable").ShowTableStyleFirstColumn = False

Remove special style from last column

ActiveSheet.ListObjects("myTable").ShowTableStyleLastColumn = False

Adding or removing stripes

Tables can have a banded rows and or columns.

Sub ChangeStripes()

'Apply column stripes
ActiveSheet.ListObjects("myTable").ShowTableStyleColumnStripes = True

'Remove row stripes
ActiveSheet.ListObjects("myTable").ShowTableStyleRowStripes = False

End Sub

Some alternative codes you could use are:

Remove column stripes

ActiveSheet.ListObjects("myTable").ShowTableStyleColumnStripes = False

Apply row stripes

ActiveSheet.ListObjects("myTable").ShowTableStyleRowStripes = True

Set default Table Style

The following macro will set the default table style.

Sub SetDefaultTableStyle()

'Set default table style
ActiveWorkbook.DefaultTableStyle = "TableStyleMedium2"

End Sub

Looping through Tables

The macros in this section will through all the tables on the worksheet or workbook.

Loop through all tables on worksheet

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 Sub

Loop through all tables in a workbook

Sub LoopThroughAllTablesWorkbook()

'Create variables to hold the worksheet and the table
Dim ws As Worksheet
Dim tbl As ListObject

'Loop through eac 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 Sub

Adding & removing rows and columns

The following macros will add and remove rows, headers and totals from a Table.

Add columns into Table

Sub AddColumnToTable()

ActiveSheet.ListObjects("myTable").ListColumns.Add

End Sub

Some alternative codes you could use are:

Add column at specific position

ActiveSheet.ListObjects("myTable").ListColumns.Add Position:=2

Add rows to bottom of Table

Sub AddRowsToTable()

ActiveSheet.ListObjects("myTable").ListRows.Add

End Sub

Some alternative codes you could use are:

Add row at specific position

ActiveSheet.ListObjects("myTable").ListRos.Add Position:=1

Delete columns from Table

Sub DeleteColumnsFromTable()

ActiveSheet.ListObjects("myTable").ListColumns(2).Delete

End Sub

Delete rows from Table

Sub DeleteRowsFromTable()

ActiveSheet.ListObjects("myTable").ListRows(2).Delete

End Sub

Some alternative codes you could use are:

Delete multiple rows

ActiveSheet.ListObjects("myTable").Range.Rows("4:6").Delete

Add total row to Table

Sub AddTotalRowToTable()

'Display total row with value in last column
ActiveSheet.ListObjects("myTable").ShowTotals = True

'Change total to average
ActiveSheet.ListObjects("myTable").ListColumns("TotalColumn").TotalsCalculation = xlTotalsCalculationAverage

End Sub

Some alternative codes you could use are:

Change total calculation based on column number

ActiveSheet.ListObjects("myTable").ListColumns(2).TotalsCalculation = xlTotalsCalculationAverage

Types of Totals Calculation

xlTotalsCalculationNone
xlTotalsCalculationAverage
xlTotalsCalculationCount
xlTotalsCalculationCountNums
xlTotalsCalculationMax
xlTotalsCalculationMin
xlTotalsCalculationSum
xlTotalsCalculationStdDev
xlTotalsCalculationVar

Change Table Header

Sub ChangeTableHeader()

'Remove header
ActiveSheet.ListObjects("myTable").ShowHeaders = False

'Remover AutoFilter dropdown
ActiveSheet.ListObjects("myTable").ShowAutoFilterDropDown = False

End Sub

Some alternative codes you could use are:

Display header

ActiveSheet.ListObjects("myTable").ShowHeaders = True

Display AutoFilter drop-down

ActiveSheet.ListObjects("myTable").ShowAutoFilterDropDown = True

Getting and selecting a cells from a Table

Since Tables are primarily for storing data, we need to look at ways to select and get data from a Table.

Get a value from an individual cell within a Table

The following macro will retrieve the value in the Table in row 2, column 4 to display in a message box.

Sub GetValueFromTable()

MsgBox ActiveSheet.ListObjects("myTable").DataBodyRange(2, 4).value

End Sub

Select the entire Table

The following macro will select the entire Table.

Sub SelectTable()

ActiveSheet.ListObjects("myTable").Range.Select

End Sub

Select the data within a Table

The following macro will select the data within the Table, but not the Header or Totals sections.

Sub SelectTableData()

ActiveSheet.ListObjects("myTable").DataBodyRange.Select

End Sub

Select an entire column

The following macro will select the second column in the Table.

Sub SelectAnEntireColumn()

ActiveSheet.ListObjects("myTable").ListColumns(2).Range.Select

End Sub

Select a column (data only)

The following macro will select the data of the 4th column of the Table.  It will exclude the Header and Total sections.

Sub SelectColumnData()

ActiveSheet.ListObjects("myTable").ListColumns(4).DataBodyRange.Select

End Sub

Select a specific column within the Header section

The following macro will select the data of the 3rd column in the Header section of the Table.

Sub SelectCellInHeader()

ActiveSheet.ListObjects("myTable").HeaderRowRange(3).Select

End Sub

Select a specific column within the Total section

The following macro will select the data of the 3rd column in the Total section of the Table.

Sub SelectCellInTotal()

ActiveSheet.ListObjects("myTable").TotalsRowRange(3).Select

End Sub

Select an entire row of data

The following macro will select the data of the 3rd row in the data section of the Table.

Sub SelectRowOfData()

ActiveSheet.ListObjects("myTable").ListRows(3).Range.Select

End Sub

Select the Header row

The following macro will select the Header section of the Table.

Sub SelectHeaderSection()

ActiveSheet.ListObjects("myTable").HeaderRowRange.Select

End Sub

Select the Total row

The following macro will select the Totals section of the Table.

Sub SelectTotalsSection()

ActiveSheet.ListObjects("myTable").TotalsRowRange.Select

End Sub

Reference parts of a table using the Range Object

Within VBA, a Table can also be referenced as if it were a normal Range Object.

Sub SelectTableAsRange()

ActiveSheet.Range("myTable[Column1]").Select

End Sub

Counting Rows and Columns

It is often useful to count the number of rows or columns.  This is a good method to know the reference number of the row or column you’ve just added.

Counting Rows

The following macro will count the number of rows within the Table.

Sub CountNumberOfRows()

Msgbox ActiveSheet.ListObjects("myTable").ListRows.Count

End Sub

Counting Columns

The following macro will count the number of columns within the Table.

Sub CountNumberOfColumns()

Msgbox ActiveSheet.ListObjects("myTable").ListColumns.Count

End Sub

Show Table data entry form

If a Table starts at Cell A1, there is a basic data entry form which can be displayed.

Sub ShowDataEntryForm()

'Only works if Table starts at Cell A1
ActiveSheet.ShowDataForm

End Sub

Check if a table exists

The following macro will check if a Table already exists within a workbook.

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 Sub

Find out if a table has been selected, if so which

The following macro will find the name of the selected Table.

Sub SimulateActiveTable()

Dim ActiveTable As ListObject
Dim tbl As ListObject

For Each tbl In ActiveSheet.ListObjects

    If Not Intersect(ActiveCell, tbl.Range) Is Nothing Then

        Set ActiveTable = tbl

    End If

Next tbl

If ActiveTable Is Nothing Then

    MsgBox "Select an Excel Table and try again"

End If

End Sub

Add a slicer to a table (Excel 2013 onwards only)

The following macro will add a Slicer to the Table for a field called “Field Name”.

Sub AddSlicerToTable()

ActiveWorkbook.SlicerCaches.Add2(Source:=ActiveSheet.ListObjects("myTable"), _
    SourceField:="Field Name").Slicers.Add SlicerDestination:=ActiveSheet, _
    Name:="mySlicer", Caption:="mySlicer", Top:=50, Left:=50, _
    Width:=100, Height:=200

End Sub

30 thoughts on “VBA code to control and manage Excel Tables

    • Excel Off The Grid says:

      Hi Hans,

      To assign values to an entire row from a variant array you could use something similar to this:

      ActiveSheet.ListObjects("myTable").ListRows(2).Range.Value = myArray
      

      This shows that you can get the range from the ListRow.

  1. Diederik says:

    Hi, is there a VBA way to change the names of all tables in a workbook. I have 30 tables in 1 workbook which are consolidated with power query. But now I want to rename them all. Can this be done with VBA? Or did I miss something in this otherwise superb post?

    • Excel Off The Grid says:

      I am sure it would be much quicker to rename the tables manually, then edit the M code in the Power Query editor.

  2. Ludo says:

    Hi,
    I have a table with different headers, let say Unit1, Unit2, ….
    Each column hold data, but the data rows/column are different.

    Example
    Row 1 = Column Header (Unit1, Unit2, …)
    Unit1 column has 30 data entries (row2 until 31).
    Unit2 column has 45 data entries (row 2 until 46).

    What i want to obtain is to count the rows containing data for a certain header.
    so for Unit1 this should result in 30, and in case of Unit2, this should be 45.
    once i can count this, adding 1 gives me the free cell under the column data where i can place the new data.

    I loop now through the column until i reach an emty cell, but can this be done more efficient?
    following code throws an Run-time error 9:

    activesheet.listobjects(“LegenKast2[#LegenKast_OIT_V2]”).listRows.count

    Best regards,
    Ludo

    • Excel Off The Grid says:

      Hi Ludo,

      You do not need to include the column header in the ListObjects collection. It should be like this:

      ActiveSheet.ListObjects("LegenKast2").ListRows.Count
      

      Hope that helps.

  3. Ludo says:

    Hi again,
    the code:
    ActiveSheet.ListObjects(“LegenKast2”).ListRows.Count
    gives me the total row count of the entire table, not the count of data in a specified table column.

    My table looks like here below.

    Unit1 Unit2 Unit3 Unit4
    33 87 41 43
    87 67 71 15
    19 14 68 77
    11 33 88 0
    5 54 43 19
    114 160 83 9
    26 55 64 15
    39 69 27 16
    39 428 133 25
    19 54 26 8
    8 37 24 8
    27 6 71 12
    31 84 0 16
    31 166 21 17
    28 54 69 13
    30 8 75 15
    27 58 82 16
    36 52 8
    56 48 18
    81 21
    76 15
    19
    14
    22
    I think i need to go to the specific column, and then use the following code:
    activesheet.Range(ActiveCell, ActiveCell.End(xlDown)).Rows.count

    This returns the last row containing data.
    In the example above this result in 17 for Unit1, and 21 for Unit2

    Important remark: you need to be in the table and use the xlDown method.

    Using the code :
    Activesheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).row
    returns the last row in the table.
    In the example above this result in 379, and is the last table row.

    So I guess I found the solution.
    Thanks anyhow for your answer.
    Best regards,
    Ludo

    • Excel Off The Grid says:

      Hi Jorge,

      We can use the CountA worksheet function within the VBA:

      MsgBox WorksheetFunction.CountA(ActiveSheet.ListObjects("myTable").DataBodyRange)
      

      The example above will output 0 if the Table is empty.

      • Jorge Cabral says:

        HI

        It seems to me that the solution you present only works if the table has more than one row in addition to the header.
        With only one line gives: “Run-Time error ‘5’:
        Invalid procedure call or argument.

        Any other solution?

        Many thanks
        Jorge

        • Excel Off The Grid says:

          Hi Jorge – I don’t get the same problem as you describe. If there is one row the macro still works.

          • Jorge Cabral says:

            Hi
            I can send you an exemple.
            When I create a table, and run the statement that you provided, the result is indeed zero.
            If I write something in the table, and then I delete all those lines, it starts to give me the error.

  4. megabirger says:

    A single visible row in a table can be a empty row or no row.
    Following code can tell you what the row is.

    With Range(“mytable”).ListObject
    If .DataBodyRange Is Nothing Then
    MsgBox “No row in table.”
    Else
    MsgBox “Row exist in table.”
    End If
    End With

  5. Peter Robbins says:

    This is excellent

    Is it possible to replace the “myTable” with a variable so that using the loop through all tables option the table name can be set as a variable and fed into he line?
    ActiveSheet.ListObjects(“myTable”).TotalsRowRange(3).Select

    I have tried removing the parentheses but no success
    ActiveSheet.ListObjects(VariableName).TotalsRowRange(3).Select

    • Excel Off The Grid says:

      If you’re looping through the Tables collection (as shown in the Looping Through Tables section above), then you could try the following:

      tbl.TotalsRowRange(3).Select
    • Excel Off The Grid says:

      Hi Michael – I’ve not tried to do what you’re asking for. From a brief look, I don’t think it is possible – but I would love to be wrong on that.

  6. Peter Bellamy says:

    Very helpful page, thanks.
    Would this be possible, or is it too complex?
    Finding specific data in one column (6) where dates in an another column (1) are in a given range, then changing the data in column 6 when there was a match.

    • Excel Off The Grid says:

      Hi Peter – “Possible” and “complex” and relative to your experience with VBA. Based on what you’ve described, yes I believe it can be achieved.

  7. William Bell says:

    Love this info on tables; thank you!

    You give this as the way to select a single column:

    Sub SelectColumnData()
    ActiveSheet.ListObjects(“myTable”).ListColumns(4).DataBodyRange.Select
    End Sub

    Using the ListColumns / DataBodyRange approach to navigating around a table, I was wondering how multiple rows can be selected?

    Thank you for your help”

    • Excel Off The Grid says:

      Hi William

      To select multiple ranges, you can use the VBA’s Union operator. Here is an example:

      Union(ActiveSheet.ListObjects("myTable").ListRows(4).Range, _
      ActiveSheet.ListObjects("myTable").ListRows(1).Range, _
      ActiveSheet.ListObjects("myTable").ListRows(3).Range).Select
      
      • William Bell says:

        Thank you for your very speedy reply; using UNION is not something I would have thought of.

        Unfortunately it doesn’t quite work as I’d hoped because it also selects cells in the table header row (NB – I used ListColumns instead of ListRows that you used in your example). It’s also, no offense intended, a little on the long / clunky side 🙁

        My use case is in a Change Event handler where I’m only interested in changes to certain databodyrange columns.

        The way I do things now is by working out the row and column numbers of the changed cell and then testing if they fall within range, but I was hoping to use the Intersect(range1, range2) approach feels more intuitive.

        I know I can reference a subset of table columns (minus header) using:

        Range(“MyTable[Col1]:MyTable[Coln]”)

        which is certainly very concise, but comes with the risk that the table name gets changed / column names get changed.

        Summing up, it doesn’t seem like there’s a way to do what I want via Excel’s table object model…

        • Excel Off The Grid says:

          Hi William,

          You can use the DataBodyRange to just select the data, like this:

          ActiveSheet.ListObjects("myTable").ListColumns(4).DataBodyRange.Select

          You could also use a With statement to make the code much shorter.

          From what you’ve described, you could use a combination of Intersect and Union to achieve your requirements.

  8. Taimo says:

    Hi,

    I have a table with 3 columns and I am trying to remove duplicates of the first column like this (2 options):

    Option1:
    ws_to.ListObjects(“Table5”).Range.RemoveDuplicates Columns:=1, Header:=xlYes

    Option2:
    ws_to.ListObjects(“Table5”).ListColumns(1).Range.RemoveDuplicates Columns:=1, Header:=xlYes

    Both options do not work as expected i.e. code removes stuff also from Table5’s other two columns which is not the intention.
    Any idea how to solve this?

    • Excel Off The Grid says:

      Hi Taimo,

      This is how Tables are supposed to work. Tables have row context, therefore all the columns in a row are related and can’t be treated like a standard range. If you try this manually, you will get the same end result.

      I recommend you find a method that works manually, then try to code the solution to follow that methodology.

  9. warren kinny says:

    Hi. Very helpful page. Thanks. 🙂

    Just a couple of teensy corrections :

    1. You used the sub name “CountNumberofRows” twice when you meant “CountNumberofColumns” the second time.

    2. You have the same text “Select the entire Table” twice as a Heading when the second time you mean “Select the DATA SECTION of a Table”.

    Easy little glitches to make when cutting and pasting 🙂

    Cheers.
    Warren K.

    • Excel Off The Grid says:

      Hi Warran – thank you. Unfortunately, errors can (and often do) creep through, so I appreciate the feedback.

Leave a Reply

Your email address will not be published. Required fields are marked *