Page 173 - Excel Progamming Weekend Crash Course

k540629 ch11.qxd 9/2/03 9:34 AM Page 148

148 Saturday Afternoon

Referencing Entire Rows and Columns

You can use the EntireRow and EntireColumn properties to access full rows
and columns within a range. For example, Range(“B1”).EntireColumn refer-
ences the entire column B, and Range(“B9”).EntireRow references all of row
9. I present an example of using these properties in the section “Adding and
Deleting Rows and Columns” later in this session.

You can use the Rows property with or without an argument (Columns works the same way):
Rows without an argument returns a range containing all rows in the range or
worksheet.
Rows(n) returns the nth row in the range or worksheet.
The following are some examples. To reference the first column in the active worksheet:

Columns(1)
To reference the second row in the range r1:

r1.Rows(2)
To reference all columns that are spanned by range r1:
r1.Columns

To reference the third column (column C) in the specified worksheet:

Worksheets(“Summary”).Columns(3)
Remember that the Rows and Columns properties return a Range object, and you can use
any of the Range object’s properties and methods to work with the columns.

The Range object was covered in detail in Session 10.

Cross-Ref
For a more complete example of using the Columns property, look at the program in
Listing 11-3. This program assigns the Currency format to the specified columns in the
active worksheet.

Listing 11-3 Using the Columns property

Public Sub FormatColumnsAsCurrency(first As Integer, number As Integer)
‘ Assigns the currency format to columns in
‘ the active worksheet. The “first” argument
‘ identifies the leftmost column (A=1, etc.)