- The Range Object
- Syntax for Specifying a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Using the Union Method to Join Multiple Ranges
- Using the Intersect Method to Create a New Range from Overlapping Ranges
- Using the IsEmpty Function to Check Whether a Cell Is Empty
- Using the CurrentRegion Property to Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
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