|
|
We already know that a worksheet organizes
its information in columns. To show the values in a worksheet, each column holds a particular value that corresponds to another
value in the same horizontal range. The group of values that correspond to the same horizontal
arrangement is called a row.
|
To support the rows of a worksheet, the Worksheet
class is equipped with a property named Rows. Therefore, to refer
to a row, you can use the Worksheets collection or the Worksheet object to
access the Rows property. Another way you can refer to rows is by using the Range
object.
To identify a row, indicate its worksheet and you can pass its number to the
parentheses of the Rows collection. Here is an example that refers
to the 5th row of the second worksheet of the current workbook:
Sub Exercise()
Workbooks.Item(1).Worksheets.Item(2).Rows(5)
End Sub
As reviewed for the columns, this code would work only
if the second worksheet of the current workbook is displaying. If you run
it while a worksheet other than the second is active, you would receive an
error. To access any row, omit the Workbooks and the Worksheets
collections.
As mentioned already, you can refer to a row using the Range
object. To do that, pass a string to the Range object. In the
parentheses, type the number of the row, followed by a colon, followed by
the number of the row. Here is an example that refers to Row 4:
Sub Exercise()
Range("4:4")
End Sub
If you want to refer to the rows more than once, you
can declare a variable of type Range and initialize it using the Set
operator and assign it the range you want. Here is an example:
Sub Exercise()
Dim SeriesOfRows As Range
Set SeriesOfRows = Workbooks.Item(1).Worksheets.Item("Sheet1").Range("4:4")
SeriesOfRows.Whatever
End Sub
|
Identifying a Group of Rows
|
|
A group of rows is said to be in a range if they are
next to each other. To refer to rows in a range, in the parentheses of the
Rows collection, pass a string that is made of the number of the row from
one end, followed by a colon, followed by the row number of the other end.
Here is an example that refers to rows from 2 to 6:
Sub Exercise()
Rows("2:6")
End Sub
The rows of a group qualify as non-adjacent if they
are or they are not positioned next to each other. To refer to
non-adjacent rows, pass a string to the Range collection. In the
parentheses, type the number of each row followed by a colon, followed by
the same number. These combinations are separated by commas. Here is an
example that refers to Rows 3, 5, and 8:
Sub Exercise()
Range("3:3, 5:5, 8:8")
End Sub
To refer to all rows of a worksheet, use the Rows
name. Here is an example:
Sub Exercise()
Rows
End Sub
To support row selection, the Row class is
equipped with a method named Select. Therefore, to programmatically
select a row, access a row from the Rows collection using the
references we saw earlier. Then call the Select method. Here is an
example that selects Row 6:
Sub Exercise()
Rows(6).Select
End Sub
We also saw that you could refer to a row using the
Range object. After accessing the row, call the Select method. Here is an
example that selects Row 4:
Sub Exercise()
Range("4:4").Select
End Sub
When a row has been selected, it is stored in an object
called Selection. You can then use that object to apply an action
to the row.
|
Selecting a Group of Rows
|
|
To programmatically select a range of rows, refer to the
range using the techniques we saw earlier, then call the Select method.
Here is an example that selects rows from 2 to 6:
Sub Exercise()
Rows("2:6").Select
End Sub
To programmatically select non-adjacent rows, refer to
them as we saw earlier and call the Select method. Here is an
example that selects Rows 3, 5, and 8:
Sub Exercise()
Range("3:3, 5:5, 8:8").Select
End Sub
To programmatically select all rows of a worksheet,
call the Select method on the Rows collection. Here is an example:
Sub Exercise()
Rows.Select
End Sub
When many rows have been selected (whether adjacent or
not), their selection is stored in an object named Selection. You can
access that object to apply a common action to all selected rows.
To support the height of a row, the Row object is
equipped with a property named
RowHeight. Therefore, to
programmatically specify the height of a row, access the row using a
reference as we saw earlier, access its
RowHeight property and
assign the desired value to it. Here is an example that sets the height of
Row 6 to 2.50
Sub Exercise()
Rows(6).RowHeight = 2.5
End Sub
To provide the ability to add a new row, the Row
class is equipped with a method named Insert. Therefore, to
programmatically add a row, refer to the row that will be positioned below
the new one and call the Insert method. Here is an example:
Sub Exercise()
Rows(3).Insert
End Sub
To programmatically add new rows, refer to the rows
that would be below the new ones, and call the Insert method. Here is an
example that will add new rows in positions 3, 6, and 10:
Sub Exercise()
Range("3:3, 6:6, 10:10").Insert
End Sub
No comments:
Post a Comment