VBA Blogs: Same Same But Different

This is the tenth blog in the series about using ListObjects to manipulate Tables within an Excel workbook in VBA. Today we will be reviewing ListColumns and how it applies to ListRows.

ListColumns can be really confusing. Using ListColumns references ALL the columns, but using ListColumns(Index) which references a specific column.

Remember, in VBA, one can press "." and the applicable method/properties that apply to that object comes up? This is what can be done with ListColumns:

• Add: lets us add a column. By default it will be added at the end of the table
• Application: returns the name of the application from which the object belongs to (in this case Excel)
• Count: returns the number of columns
• Creator: returns the name of the author of the object
• Item returns the column as an object. ListColumns.Item(Index) is identical to using ListColumns(Index) in terms of referencing a specific column
• Parent returns the overall owner of the object, in this case, being “MyTable”.

All of these things are identical in ListRows rows. Simply interchange column in the above list with row!

Notice how there is no Delete property here and is done on a row/column indexed basis. This is because invoking delete would either delete your entire data set (on ListRows) or the entire table (on ListColumns). If that is indeed what one wishes to do, then using DataBodyRange or ListObject table itself would be more intuitive and understandable.

Come back next week to look at the properties of individual rows/columns.