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

VBA code to control and manage Excel Tables

VBA Code Snippets

.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.


Advertisement:

 

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 = True

Remove special style from last column

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

 

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

'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

Advertisement:

 

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

Advertisement:

 

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 entire 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

Advertisement:

 

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).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

Advertisement:

 

Counting Columns

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

Sub CountNumberOfRows()

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


Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are: