Page 168 - Excel Progamming Weekend Crash Course

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

Session 11 — Working with Columns, Rows, and Cells 143

For the range r1, for example, the code would look similar to this:

For r = 1 to r1.Rows.Count
For c = 1 To r1.Columns.Count
‘ Code to manipulate r1.Cells(r, c) goes here.
Next c
Next r

The Range object has Width and Height properties. You might think that
these properties give the size of the range in terms of rows and columns,
Note but they do not. Rather, they give its size in terms of screen pixels. You
must use the Rows. Count and Columns.Count properties to determine
the number of rows and columns in a range.
The program in Listing 11-1 illustrates the use of the Cells property. The program goes
through all the cells in the current selection and determines the largest and smallest values
in the cells; then displays a dialog box with the results (as shown in Figure 11-1).

Listing 11-1 Using the Cells property to loop through all cells in a range

Public Sub LargestSmallestValues()
‘ Displays a dialog box with the largest
‘ and smallest values in the current selection.

Dim row As Integer, col As Integer
Dim largest_value, smallest_value
Dim r As Range
Dim s As String
‘ Make sure selection is a range.
If TypeName(Selection) <> “Range” Then
MsgBox (“There is no range selected”)
Exit Sub
End If

Set r = Selection

‘ Start with the first cell value.
largest_value = r.Cells(1, 1).Value
smallest_value = r.Cells(1, 1).Value

‘ Loop thru all rows.
For row = 1 To r.Rows.Count
‘ Loop thru all columns.
For col = 1 To r.Columns.Count
If r.Cells(row, col).Value > largest_value Then
Continued