Columns, Rows, and Cells of a Worksheet

The Columns of a Worksheet

Introduction to Columns

Microsoft Excel provides easy means of creating a list. It does this by pre-defining categories of items in vertical sections called columns. Each column displays one or two letters or characters in an area referred to as the column header. The most left column has a label of A. The second has a label of B, and so on:

The columns are stored in a property called Columns that belongs to the current worksheet, that is, the worksheet that accesses it. To programmatically access a column, pass the label of column header, as a single range, to the Columns collection. Consider the following code:

Private Sub CommandButton1_Click()
    Columns("D:D")
End Sub

Writing Columns("D:D") allows you to access column D. To access a range of columns, pass the range to the Columns collection as a string. Consider the following code:

Private Sub CommandButton1_Click()
    Columns("F:J")
End Sub

Practical Learning: Checking Columns

1. Open the Cherry Pumpkin Day Care1 file
2. To see the number of columns, click and hold the mouse on the right arrow button of the horizontal scroll bar:
3. Release the mouse and press Ctrl + Home

Columns Selections

Before performing an action on a column, you may have to select it first. Depending on the assignment, you may need to select more than one column.

You can select a column or a group of columns using the mouse, the keyboard, a combination of both, or using code. To select a column, you can click (with the left mouse button) a column header; it would get selected and all small boxes under it. To select a column using the keyboard, click anything under it, then press and hold Ctrl. While Ctrl is down, press the Space bar and release Ctrl.

You can also select more than one column. Selecting columns in a range consists of selecting adjacent columns. To perform this type of selection, you can use either the mouse or a combination of the mouse and the keyboard. To select columns in a range, click one column header and hold the mouse down. Then drag in the direction of the range. Alternatively, to select a range of columns, click one column at one end of the desired range. Press and hold Shift. Then click the column at the other end, and release the mouse.

Random selection consists of selecting columns that are not adjacent. For example, this allows you to select columns B, D, and H. To do this, click one column header, press and hold Ctrl. Then click each desired column header. When you have selected the desired columns, then release the mouse.

To programmatically select a column, pass the label of column header, as a single range, to the Columns collection, and call the Select() method. Consider the following code:

Private Sub CommandButton1_Click()
    Columns("D:D").Select
End Sub

This allows you to select column D. To select a range of columns, pass the range to the Columns collection as a string. Consider the following code:

Private Sub CommandButton1_Click()
    Columns("F:J").Select
End Sub

This code allows you to select the range of columns from F to J.

Practical Learning: Selecting Columns

1. To select a column, click the column header D
2. To select more than one column, click column header C and hold the mouse down. While the mouse is still down, move it right until column F is selected, then release the mouse
    
3. To select columns in a range, click column header B
4. Press and hold Shift, then click column header E
5. Release Shift
6. To select columns at random, click column header H
7. Press and hold Ctrl
8. Click column headers B, E, and C
9. Release Ctrl
10. Click any box under column header D
11. To select a column with the keyboard, for example column D, press Ctrl + Space

The Records or Rows of a List

Introduction to Rows

As mentioned for the list of videos above, when creating a list, you typically enter a necessary piece of information under each column until you reach the right end of the list. The values that share the same line are called rows. To make it easy with the creation of a list, Microsoft Excel provides ready made rows and each is identified with a number. The most top row is numbered 1. The row under it is named 2, and so on. The gray area that displays the number of the row can be referred to as the row header.

Microsoft Excel provides 65536 rows:

You can use just a few of them for your assignment but all of them are always available.

The records of a worksheet are stored in its Rows collection. To access a row, pass its single range to Rows. Here is an example:

Private Sub CommandButton2_Click()
    Rows("12:12")
End Sub

This example indicates that you want to access row 12. To access a range of records, pass it to the Rows collection. Consider this:

Private Sub CommandButton2_Click()
    Rows("4:18")
End Sub

This code indicates that you want to access the range or rows from 4 to 18.

Rows Selections

As done with columns, you can select one row or a group of rows. You can also select rows at random. You can perform selections using the mouse, the keyboard, or a combination of both.

To select a row, you can just click its row header. To select a row using the keyboard, click any box on its right, press and hold Shift. While Shift is still down, press the Space bar and release Shift.

To select a range of rows, you can click one row header and hold the mouse down. Then drag in the direction of the range. Alternatively, to select a range of rows, click one row at one end of the desired range. Press and hold Shift. Then click the row at the other end, and release the mouse.

To select rows at random, click one row header, press and hold Ctrl. Then click each desired row header. When you have selected the desired row, then release the mouse.

To select a row, pass its single range to the Rows collection and call the Select() method. Consider the following example:

Private Sub CommandButton2_Click()
    Rows("8:8").Select
End Sub

This code will select row 8. In the same way, to select a range or records, pass the range to the Rows collection and call the Select() method. Consider the following example:

Private Sub CommandButton2_Click()
    Rows("6:10").Select
End Sub

This code will select rows from 6 to 10.

Practical Learning: Selecting Rows

1. To select a row, click the row header 4
2. To select more than one row, click row 2 and hold the mouse down. While the mouse is still down, move it down until row 8 is selected
3. Release the mouse
4. Click any box on the right side of row 5
5. To select a row, for example row 5, press Shift + Space
6. To deselect, press the right arrow key

A Row's Height

To display the contents of boxes on its right, a row uses a set height. The height is the distance from the top to the lower borders of the row.

To change the height of a row, you can position your mouse on its lower border, click and drag down until you get the desired height. Then release the mouse. You can also right-click a row header and click Row Height. This calls the Row Height dialog box that allows you to enter the numeric value of the desired height and press Enter.

After selecting various rows, you can also change the heights of a group of rows.

The height of a row or a collection is represented by the RowHeight property of the Rows collection. Based on this, to change the height of a row, pass its single selection to the Rows collection, access its RowHeight property and assign it the desired height. Here is an example:

Private Sub CommandButton2_Click()
    Rows("16:16").RowHeight = 2.24
End Sub

This example will change the height of the 16th row to 2.24. In the same way, to change the heights of various rows, pass their range to the Rows collection, access the RowHeight property and assign the desired value.

Practical Learning: Controlling Rows Height

1. The DAWN Report2 workbook should still be opened.
   Press Ctrl + Home
2. Right-click row 2 and click Row Height...
3. Type 24
    
4. Press Enter and press Ctrl + Home
5. Position the mouse on the separator of rows 3 and 4 until the mouse turns into a narrow line with vertical arrows
6. Then click and drag down slowly. A small yellow box allows you to evaluate the height
    
7. When you get to Height: 24, release the mouse
8. Select rows 6 and 7
9. Right-click row 6 and click Row Height...
10. Type 17.25 and click OK
11. Press Ctrl + Home
12. To save the workbook, press Ctrl + S

Hide and Unhide Rows

As mentioned earlier, Microsoft Excel makes all of the rows of a sheet available while you are working. If at a certain time you don't need to display a certain row, you can hide it. In the same way, you can hide more than one rows.

To hide a row, while that row or a box on its right is selected, on the main menu, click Format -> Row -> Hide. The row will disappear from the screen without being deleted. To unhide the row, on the main menu, click Format -> Row -> Unhide.

To programmatically hide a row, first select. Then, access the Hidden property of the EntireColumn object of Selection. Consider the following example:

Private Sub CommandButton2_Click()
    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.

Cells

Introduction

A cell is a box at the intersection of a column and a row. The cell is the actual object that receives values or presents them to a user.

The cells of a worksheet are stored in a collection called Range.

Location of a cell

Whatever you are doing on a cell, it is always important to know what cell you are working on. The minimum piece of information you need about a cell is to know which one you are using at a particular time. To make this recognition a little easier, each cell has an address also called a location. This address or location also serves as the cell's primary name.

To know the location of a cell, you refer to its column and its row. The combination of the column and the row's labels provides the address or name of a cell. When you click a cell, its column header receives a border thicker than the other column headers. In the same way, the row header of a selected cell is thicker than the other row headers. To know the name of a cell, you can refer to the Name Box, which is located at the intersection of columns and rows' headers:

Cells Selection 

Before doing anything on a cell or a group of cells, you must first select it. Selecting cells is almost equivalent to highlighting a word in a text document. And like a text document, various techniques are available here.

The Active Cell
At almost any time, at least one particular cell on the worksheet is selected. A selected cell has a thick border that sets it apart from the other cells. This is important because you always need to know where the changes you are making are being applied. This means that, whatever you type or format will apply to that particular cell. Sometimes you will want to work on many cells simultaneously. Therefore, you first have to select the intended cells before proceeding.

Other Cells Selections
At times you will almost want to alter the looks of a column, a row, various columns, or various rows. Once again, you have to select them first. Another issue involved with selecting cells, rows, columns, or rows allows you to clear, freeze, or hide some of these components. Some of these issues will be addressed soon, some others will be reviewed as we move on.

To select one cell, just click it. To select another cell, click the new one.

To select cells under the same column, click and hold your mouse on one cell, then drag down (or up) to the last cell.

To select cells on the same row, click one of the cells, then drag right (or left) to the last cell.

To select cells at random, click one of the cells involved in the selection (any of the cells), press and hold Ctrl, then click each one of the desired cells. When the cells have been selected, release Ctrl.

To select cells on the same row using a combination of the mouse and keyboard, give focus to a cell on one end, press and hold Shift, then click the cell that will be at the other end. Release Shift.

To select more than one cell, press and hold Shift. If you press the right arrow key, the currently highlighted cell and the cell to its right would be selected. If you press the down arrow key, the current cell and the cell under it will be highlighted. You can also press the left or up arrow key. You can press the arrow key many times in the direction of your choice. Once you have achieved the selection you want, release the Shift key.

To select all cells on a worksheet, you can press Ctrl + A. Alternatively, you can click the button at the intersection of the column header and row headers.

To programmatically select a cell, pass its address to the Range collection and call the Select() method. Here is an example:

Private Sub CommandButton1_Click()
    Range("F14").Select
End Sub

This code will move the focus to cell F14. In the same way, to select a range of cells, pass it to the Range collection and call the Select() method. Here is an example:

Private Sub CommandButton1_Click()
    Range("B2:D6").Select
End Sub

This code will select cells from B2 to D6.

Practical Learning: Selecting Cells

1. To access the Cherry Pumpkin Day Care1 file, on the main menu, click Window and click it
2. To select one cell at a time, click cell B6
3. Click cell C4
4. Click cell E10
5. To select multiple cells on the same row, position the mouse on cell B5, your mouse turns into a big + sign
6. Click and hold down the mouse
7. Drag the mouse in the right direction until Laurie in cell E5 is selected, then release the mouse. Notice that four cells are selected
8. Position the mouse on cell D4, then click and hold the mouse down. Drag the mouse down until Hamilton in cell D9 is highlighted
9. Release the mouse. Notice that the range of cells on the same column is selected.
10. Click cell B4 and hold the mouse down
11. Drag the mouse right and down to cell D10 (Bassano) until that cell D10 is included in the highlighted range
12. Release the mouse. That selects adjacent cells in the same area.
13. To select all cells, click the button on the intersection of the column and row headers
14. To deselect, click cell C5
15. To select one cell at a time, press the down arrow key; that selects cell C6
16. Press the right arrow key; that selects cell D6
17. Press the up arrow key
18. Press the left arrow key
19. To select multiple cells on the same column, press and hold Shift (with your left hand)
20. While Shift is down, press the down arrow key twice, and then release Shift. That selects multiple cells on the same column.
21. Press the up arrow key
22. To select multiple cells on the same row, press and hold Shift
23. Press the right arrow key three times, and release Shift.
24. Press the left arrow key
25. Click cell C5
26. Press and hold Shift
27. To select adjacent cells, while Shift is still down, press the left arrow key one time and the down arrow key four times, then release Shift
28. To deselect, press the left arrow key
29. Click cell B6
30. To select adjacent cells, press and hold Shift, click cell D10, then release Shift. All cells in the range are included in the selection
31. To select cells at random, click cell D5. Press and hold Ctrl. While you are holding Ctrl, click cells B7, H10, and E11
32. Release Ctrl
33. Press Ctrl + Home
34. To select the whole worksheet, press Ctrl + Shift + Space
35. To deselect, press the right arrow key
36. To select the whole worksheet again, press Ctrl + A
37. To deselect, press the down arrow key

The Width of a Cell 

A cell in a Microsoft Excel worksheet doesn't have dimensions of its own. It uses those imposed to it. Since at least one cell in the worksheet always has focus, to change the column width under which that cell is selected, on the main menu, you can click Format -> Columns. You have a lot of options:

• If you click Width..., you will be able to type the new width value and click OK
• If you click AutoFit Selection, the column will be resized to the width of the selected cell (the active cell that has focus)
• If you are not sure or you just want to restore the default column width, click Standard Width... accept 8.43, then press Enter

We saw earlier how to change the width of a column. This also allows you to change the width of cells under that column. To use AutoFit Selection, first give focus to the cell that contains the value used as reference, then call the AutoFit() method of the Columns property of the Selection object. It can be called as follows:

Private Sub CommandButton1_Click()
    Selection.Columns.AutoFit
End Sub

This code will check the value of the selected cell and will resize the column to automatically fit that cell.

Practical Learning: Setting Column Width Using Cells

1. To access the DAWN Report2 file, on the main menu, click Window and click it. If necessary, click Sheet1
2. Click cell B7
3. On the main menu, click Format -> Column -> AutoFit Selection
4. Click column F to select the whole column
5. On the main menu, Format -> Column -> AutoFit Selection. Notice that the whole column has been resized to the widest entry
6. Click cell G8
7. On the main menu, click Format -> Column -> AutoFit Selection. Now the column has is resized to the width of cell G8

Cells Names

We saw that a cell, each cell, has a name, which is also its location, which is provided by a combination of its parent column and its parent row. Instead of using the column and row to identify a cell, Microsoft Excel allows you to change the name of a cell to your liking. In fact, you can combine cells and name the group to anything you like.

To change the name of a cell, first click it. Then, in the Name Box, replace the name with the desired name and press Enter. Alternatively, to create a new name for a cell, first select it. Then, on the main menu, click Insert -> Name -> Define... In the Names In Workbook text box of the Define Name dialog box, type the desired name and click OK.

To change the name of a group of cells, first select them, using the techniques we learned for selecting cells. Then, in the Name Box, replace the string with the new name. In the same way, to create a name for a group of cells, after selecting them, on the main menu, click Insert -> Name -> Define... In the Names In Workbook text box of the Define Name dialog box, type the desired name and click OK.

Practical Learning: Naming Cells

1. Open the DAWN Report1 file
2. To name a cell, click cell C2
3. Click in the Name Box. That highlights C2. Type MainTitle and press Enter
    
   
4. Select cells A7:D16
5. Click in the Name Box to select the current name. Type EREpisodes and press Enter
6. Select cells F7:I16
7. On the main menu, click Insert -> Name -> Define...
8. In the Define Name dialog box, replace the contents of the Names In Workbook combo box with RelatedDeaths
    
   
9. Click OK
10. To review names, select cells B7:D16
     
    
11. Save and close the file