VBA Blogs: ListColumn
12 January 2018
Welcome back to our VBA blog series. This is the eighth blog in the series about using ListObjects to manipulate Tables within an Excel workbook in VBA. Today we will be featuring ListColumns.
The ListColumns property allows us to manipulate columns within a table. Say we have the following table, with the table name of ‘Table1’:
We can select a specific column in a table using the following code in VBA:
Dim Tbl As ListObject
Set Tbl = Range("Table1").ListObject
Tbl.ListColumns(1).Range.Select
Or if we just want the data in a specific column we can use this VBA code:
Set Tbl = Range("Table1").ListObject
Tbl.ListColumns(1).DataBodyRange.Copy
From here we can copy and paste the column with this code (but being a devoted reader of our VBA blogs, you already know how to do this
That’s not the only thing we can do with ListColumns; we can also add columns. The following VBA code will insert a column at the end of the table:
Set Tbl = Range("Table1").ListObject
Tbl.ListColumns.Add
Furthermore, we can specify exactly where we want the new column by adding “Position: =2” at the end of the previous code:
Tbl.ListColumns.Add Position:=2
Our example table now looks like this:
Remember these codes alone are trivial at best, but combine them with other properties or functions and the sky is the limit!
That’s all for now, join us next week on our blog page for more on VBA! Alternatively, if you’re hungry for more VBA now, here is a page with all of our past VBA blogs.
