Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

This chapter is from the book

This chapter is from the book

Referencing Tables

Tables are a special type of range that offers the convenience of referencing named ranges, but they are not created in the same manner. For more information on how to create a named table, see Chapter 6, “Create and Manipulate Names in VBA.”

The table itself is referenced using the standard method of referring to a ranged name. To refer to the data in Table1 in Sheet1, do this:

Worksheets(1).Range("Table1")

This references the data part of the table but does not include the header or total row. To include the header and total row, do this:

Worksheets(1).Range("Table1[#All]")

What I really like about this feature 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, do this to reference the Qty column of the table:

Worksheets(1).Range("Table1[Qty]")
  • + Share This
  • 🔖 Save To Your Account