The Range Object

The following is the Excel object hierarchy:

Application ^ Workbook ^ Worksheet ^ Range

The Range object is a property of the Worksheet object. This means it requires that either a sheet be active or it must reference a worksheet. Both of the following lines mean the same thing if Worksheets(l) is the active sheet: Range("A1")

Worksheets(1).Range("A1")

There are several ways of referring to a Range object; Range("Al") is the most identifiable because that is how the macro recorder does it. But each of the following is equivalent:

Range("B3").Range("C3") Cells(5,4)

Range("A1").Offset(4,3) Range("MyRange") 'assuming that D5 has a Name of MyRange

Which format you use depends on your needs. Keep reading—it will all make sense soon!

The Range Object 61

Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range 62

Referencing Ranges in Other Sheets 63

Referencing a Range Relative to Another Range 63

Using the Cells Property to Select a Range 64

Using the Offset Property to Refer to a Range 65

Using the Resize Property to Change the Size of a Range 67

Using the Columns and Rows Properties to Specify a Range 68

Using the Union Method to Join Multiple Ranges 68

Using the Intersect Method to Create a New Range from Overlapping Ranges 69

Using the isEmpty Function to Check Whether a Cell Is Empty 69

Using the CurrentRegion Property to Quickly Select a Data Range 70

Case Study 71

Using the Areas Collection to Return a Non-contiguous Range 74

Next Steps 74

0 0

Post a comment