VBA List – A Powerful Tool for Processing Tables in Excel
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