Referring to ranges

Referencing tables

A table is a special type of range that offers the convenience of referencing named ranges. However, tables are not created in the same manner as other ranges. For more information on how to create a named table, see Chapter 6, “Creating and manipulating names in VBA.”

Although you can reference a table by using Worksheets(1).Range("Table1"), you have access to more of the properties and methods that are unique to tables if you use the ListObjects object, like this:

Worksheets(1).ListObjects("Table1")

This opens the properties and methods of a table, but you can’t use that line to select the table. To do that, you have to specify the part of the table you want to work with. To select the entire table, including the header and total rows, specify the Range property:

Worksheets(1).ListObjects("Table1").Range.Select

The table part properties include the following:

• Range—Returns the entire table.

• DataBodyRange—Returns the data part only.

• HeaderRowRange—Returns the header row only.

• TotalRowRange—Returns the total row only.

What I really like about coding with tables is the ease of referencing specific columns of a table. You don’t have to know how many columns to move in from a starting position or the letter/number of the column, and you don’t have to use a FIND function. Instead, you can use the header name of the column. For example, to select the data of the Qty column of the table, but not the header or total rows, do this:

Worksheets(1).ListObjects("Table1").ListColumns("Qty")_
   .DataBodyRange.Select