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