The ListObjects property of the Worksheet object returns the ListObjects collection. The ListObjects collection contains all the list objects on a worksheet. The ListObject object is a member of the ListObjects collection. Each ListObject object represents a table (previously known as a list) in the worksheet.

An Excel table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

Here is how a table (list) looks like. It can be identified by a small blue arrow at the right-most bottom-most cell of the table.

Whenever you import data from a file (Excel / Text / XML / JSON) or from a database (SQL / Access), the imported data is always in the form of a table.

You can also convert the selected range in Excel to a table by using the Format as Table option. For that simply select your data range, in the home tab, go to “Format as Table” and select the desired formatting. You will asked to confirm the selected data range and whether your table has headers. Input the data and click on OK and a table will be created.

To convert a table back to normal range, use the convert to range option. For that, click anywhere on the table, the design tab will be visible. In the design tab, click on convert to range.

Now, that we know what is a table let us look at how to use VBA to modify these tables.

Example 1: Loop through all the tables (lists) in a worksheet

The code below creates a new ListObjects collection which represents all the tables in a worksheet and displays the name of each of the table in the collection. The code along with the comments is self-explanatory.

Sub excelLists()
    Dim dataSheet As Worksheet
    Dim tableList As ListObjects
    Dim table As ListObject

    Set dataSheet = Sheet1

    'create a new ListObjects collection which represents all the tables in Sheet1
    Set tableList = dataSheet.ListObjects

    'Loop through each table in the tableList ListObjects collection
    For Each table In tableList

        'Display the table name
        MsgBox table.Name

    Next table

End Sub

The name of the table can be seen in the design tab. Select any cell in the table and the design tab will be shown. The name of the selected table is seen in the left side of the tab. You can edit it from here. You can also see a list of all the tables in the workbook along with their names and ranges in the Name Manager (in the Formulas tab).
Example 2: Column operations on a table
In this example we will see few of the most common operations that you can perform on the table columns. Namely, looping through all the columns of a table and printing their names, inserting columns at the end or at a specified position, deleting a particular column and copying only the data from a particular column.
Sub ListCols()
    Dim dataSheet As Worksheet
    Dim table As ListObject
    Dim tblCols As ListColumns
    Dim tblCol As ListColumn
    Dim newCol As ListColumn

    Set dataSheet = Sheet1

    'Refer a table by it's name
    Set table = dataSheet.ListObjects("Table2")

    'Get all the columns of a table
    Set tblCols = table.ListColumns

    'Loop through all the columns of a table
    For Each tblCol In tblCols
        MsgBox tblCol.Name
    Next tblCol

    'Insert a column at the last position
    Set newCol = tblCols.Add
    newCol.Name = "Last"

    'Insert a column at the third position (after title)
    Set newCol = tblCols.Add(3)
    newCol.Name = "Third"

    'Delete the fifth column (i.e. DOB)
    table.ListColumns(5).Delete

    'Copy only the data in column 1 (excluding the header)
    'We are pasting it in the "Last" column for demo
    table.ListColumns(1).DataBodyRange.Copy (Cells(3, 9))

End Sub

Here is how the above table will look after executing the code

Example 3:  Row operations
In this example let us have a look at some of the row operations in a table. As with columns, you can loop through all the rows, add and delete rows, work with totals row and header row.
Sub excelLists()
    Dim dataSheet As Worksheet
    Dim table As ListObject
    Dim tblRows As ListRows
    Dim tblRow As ListRow
    Dim newRow As ListRow

    Set dataSheet = Sheet2
    Set table = dataSheet.ListObjects("Table5")

    'Get all the Rows of a table
    Set tblRows = table.ListRows

    'Loop through all the Rows of a table
    For Each tblRow In tblRows
        'Display the value in each row for column 1
        'The row index will always start at 1, irrespective of its
        'position on the worksheet
        MsgBox table.DataBodyRange(tblRow.Index, 1)
    Next tblRow

    'Insert a Row at the last position
    tblRows.Add

    'Insert a Row at the third position (excluding header row)
    Set newRow = tblRows.Add(3)

    'Delete the fifth Row (i.e. with ID 5148)
    table.ListRows(5).Delete

    'Display the totals row at the bottom
    table.ShowTotals = True

    'Change color of only the header row
    table.HeaderRowRange.Interior.Color = vbBlue

End Sub

This is how the original table will look like after executing the code

Did you know you can convert this table into text using VBA? Read here to find out more.
See Also:
Excel VBA, Find and List All Files in a Directory and its Subdirectories
Guide to Dictionary Objects