The Rows of a Worksheet
Introduction to Rows
We already know that a worksheet organizes its information in categories called columns. To show the values in a worksheet, each column holds a particular value that corresponds to another value in the same horizontal range. While the values under a column should be the same type, the values in a horizontal range can be different. The group of values that correspond to the same horizontal arrangement is called a row.
Although each row in a list has a value for each column, it is not unusual to have empty areas under a certain column and sometimes a row would even have only one value even though there are many columns available.
As a spreadsheet application, when Microsoft Excel starts it creates the rows you will need. As a matter of fact, Microsoft Office Excel 2007 creates 1,048,576 rows.
Like the columns, each row is labeled. The rows are labeled from Row 1 to Row 1,048,576. These labels show on small boxes on the left side of the Microsoft Excel interface. Each box that shows the label of a row is called a row header:
You can use just a few of the rows for your assignment but all of them are always available.
When using a row, you can click it or use the keyboard to get to it. You can also right-click a row. When you do this, a menu would appear:
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
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
As done with columns, you can select one row or a group of rows. You can perform selections using the mouse, the keyboard, or a combination of both.
To select a row using the mouse, position the mouse on a row header. The mouse cursor would change into a right-pointing arrow. Then click:
You can also use only the keyboard. To select a row using the keyboard, make sure a box on its right side is selected. Press and hold Shift. While Shift is still down, press the Space bar and release Shift
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.
You can also select more than one row at the same time. You can perform selections using the mouse, the keyboard, or a combination of both.
To select a range of rows using the mouse, click one row header and hold the mouse down. Then drag in the direction of the range:
To select many rows using only the keyboard, select the starting row. Press and hold Shift, then press either the up or the down arrow key. When the range of rows has been selected, release Shift
You can also use a combination of the mouse and the keyboard to select one or more 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 display the contents of boxes on its right, a row uses a certain height. The height is the distance from the top to the lower borders of the row. There are various techniques you can use to change the height of a row, using approximations or being precise.
To manually change the height of a row, position the mouse on the lower border that separates it from the next row (unless it is the last row). Here is an example:
Click, then drag up or down until you get the desired height, then release the mouse.
You can also resize a group of rows. First, select the rows as we described above. Then position the mouse on the bottom border of one of the selected rows. Click and drag up or down in the direction of your choice until you get the desired height. Then release the mouse.
To undo this action:
If one of the boxes on the right side of a row header is too short or too tall, you can change the height of the row. To do this:
To undo any of these actions:
You can use a dialog box to set exactly the desired height of a row or a group of rows. To specify the height of a row:
To specify the same height for many rows:
This would call the Row Height dialog box where you can type the desired value and click OK or press Enter.
To undo any of these actions:
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
In our introduction, we saw that Microsoft Excel creates and makes available over a million rows you can use when working on a worksheet. In the next lesson, we will see that you can use the boxes on the right sides of the row headers to create the necessary values of your worksheet. One of the result is that, at times, you will want to create a row between two existing rows. Microsoft Excel provides all the means you need to add one or more new rows to a list. When you add a new row, Microsoft Excel removes the last row to keep the count to 1,048,576.
You can only insert a new row above an existing one. To insert a new row:
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 add more than one row, first select the rows, whether in a range or randomly. Then:
If you select rows randomly (non-adjacent), a new row would be created below each of the selected rows.
To undo any of these actions:
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
If you have a row you do not need anymore, you can delete it. To delete a row:
To support row removal, the Row class is equipped with a method named Delete that takes no argument. Based on this, to delete a row, access it using a reference as we saw earlier, and call the Delete method. Here is an example:
Sub Exercise() Rows(3).Delete End Sub
Of course, you can use either the Rows collection or the Range object to refer to the row.
To delete more than one row, first select the rows, whether in a range or randomly. Then:
To undo any of these actions:
To delete a group of rows:
Sub Exercise() Range("3:3, 6:6, 10:10").Delete End Sub
Click and hold your mouse down. Drag up or down. Two horizontal lines would guide you. When you get the row to the desired location, release the mouse.
When you move a row, its boxes move but it assumes the appropriated number based on its new location so the numeric sequence would be kept.
To move a group of rows, select them. Position the mouse on one of the horizontal lines of the selection:
Click and hold your mouse down. Drag up or down. Horizontal lines would guide you. When you get the rows to the desired location, release the mouse. When you move the rows, their boxes move but they assume the numbers of the new location with the appropriate numeric sequence.
When moving one or more rows, their location changes and they keep the values on the right sides of their row headers. Sometimes, you may not want to move the row(s) but only their values. This means that you can copy the rows to the clipboard and paste them where you want.
To copy a row to the clipboard:
After copying a row to the clipboard, all of its values are made available. To put those values on another row:
Instead of moving a row and its values, you can instead moving only its values but keep the row wherever it is located. To support this operation, you can cut a row to the clipboard and paste its values to another row.
To temporarily move a row to the clipboard to wait to be pasted:
After cutting a row to the clipboard, if you do not want to paste it anywhere, you can press Esc. If you want to paste it to another row:
To temporarily move the contents of many rows to the clipboard to wait to be pasted, select the rows as we saw earlier. Then:
If you want to paste the values to another group of rows:
When you paste, the values of the boxes under the original rows would be emptied.
The rows of a list display their values as necessary. Sometimes, you may not need to see all the rows. You can display some of the rows you need and (temporarily) hide those you do not need. You can hide one row or you can hide as many as you want.
To hide a row:
When a row has been hidden, its row header disappears from the numeric sequence and the line between the previous neighbors is thicker than the other dividing lines.
To hide many rows, select the rows. Then:
To programmatically hide a row, first select. Then, access the Hidden property of the EntireRow object of Selection. Here is an example:
Private Sub Exercise() Rows("6:6").Select Selection.EntireRow.Hidden = True End Sub
This code example will hide row 6. In the same way, to hide a group of rows, first select their range, then write Selection.EntireRow.Hidden = True.To reveal the hidden rows:
When using a long list, you can scroll up and down as necessary. While scrolling down, some rows would be disappearing from the top. When scrolling down, some rows would disappear from the bottom. If you want, you can freeze a row so that, when you scroll down, a top row the rows above it would not move.
To freeze a row, click the row header of the row that will lead the moving rows. On the Ribbon, click View. In the Window section, click Freeze Panes, and click Freeze Panes.
Instead of freezing the rows, you can divide the Microsoft Excel series of rows into two groups. Then you can scroll one group while the other is fixed.
To split the rows in two groups, click a row header. On the ribbon, click View. In the Window section, click Split. This would display a bar.
As mentioned for the columns, the similarities between the freezing and splitting are as follows:
The differences between the freezing and splitting are as follows:
|Home||Copyright © 2007-2009, FunctionX|