Chapter 8: Ranges and Cells

 < Day Day Up > 



Before any work can be done with the information inside a workbook, you need to be able to access it. You've already looked at accessing the larger elements of the Excel Object Model, namely the Microsoft Excel application itself and workbooks and worksheets. In this chapter, you will learn how to work with cells and ranges of cells.

Basic Range Manipulations

A cell is the basic working unit inside an Excel worksheet, but within the Excel Object Model, a Range object is the basic working unit. You use a Range object to work with either groups of cells or an individual cell.

Finding the Active Range

Although there is no ActiveRange object to use, there are other ways of working with the currently selected range. The most common method is to use the Selection object. The Selection object will return whatever the current selected object is, whether it is a range of cells or a chart. Most often it will refer to a cell or range of cells. Manipulating the Selection object lets you control the actions within a cell as if you were actually performing them step by step using the keyboard or mouse. For example, you could use the following code to display the values in the selected cells in bold type:

Selection.Font.Bold = True
Note 

Remember that active is not synonymous with selected. You can select every cell in a worksheet by pressing Ctrl+A, but only one cell (usually A1 if you press Ctrl+A) is the active cell.

Selecting a Range

The Select method of a Range object provides various ways to select a range of cells. Many of the procedures are similar to selecting cells using the keyboard, making it very easy to emulate the way you would work if you were actually typing in the keystrokes needed to select the cells.

The following example uses the CurrentRegion property of the ActiveCell object to select the range of cells that are currently being used within the worksheet. The range is copied to the clipboard, pasted onto a new worksheet, and then necessary formatting is applied and the value contents are erased, leaving blank cells for a new year's worth of information in the SalesByCategory.xls workbook.

click to expand

Sub InsertNewSheet()

Range("C1").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "New Year"
Sheets("New Year").Select
Range("C1").Activate
ActiveSheet.Paste
Columns("C:H").EntireColumn.AutoFit
Range("D2:G13").Select
Selection.ClearContents

End Sub

Besides using the CurrentRegion property of the ActiveCell, you can also use the End method to extend a range beyond the ActiveCell. When used with one of the values listed in Table 8 1, Excel will extend a range using the same rules as when you use the End key combined with one of the arrow keys to move to the end of a row or column.

The following example searches all of the time entries for one month in the Y2001ByMonth.xls workbook for the highest entry. Once the highest entry is found, Excel extends the range upwards and downwards to include all entries for that time period and changes the fill color of the cells to blue.

click to expand

Sub HighLightTimeFrame()
Dim MyCell As Range, strAddress As String
Dim sngMaximum As Single

sngMaximum = 0
For Each MyCell In Range("D6:O36").Cells
If MyCell > sngMaximum Then
sngMaximum = MyCell
strAddress = MyCell.Address
End If
Next MyCell

Range(strAddress).Activate
Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
Selection.Cells.Interior.ColorIndex = 41
End Sub

Table 8-1 lists the methods available to the Range object and describes how the method affects which cells are selected.

Table 8-1: Range Selection Methods

Method

Action Performed

.End(xlDown)

Extends range downward.

.End(xlUp)

Extends range upward.

.End(xlToRight)

Extends range to the right.

.End(xlToLeft)

Extends range to the left.

.SpecialCells(xlCellTypeAllFormatConditions)

Extends range to include cells with any formatting changes applied. Excel begins searching from cell A1, not from the ActiveCell.

.SpecialCells(xlCellTypeAllValidation)

Extends range to first cell containing validation rules.

.SpecialCells(xlCellTypeBlanks)

Extends range to first blank cell.

.SpecialCells(xlCellTypeComments)

Extends range to first cell containing a comment.

.SpecialCells(xlCellTypeConstants)

Extends range to first cell containing a constant.

.SpecialCells(xlCellTypeFormulas)

Extends range to first cell containing a formula.

.SpecialCells(xlCellTypeLastCell)

Extends range to the left and downward to the cell last used.

.SpecialCells(xlCellTypeSameFormatConditions)

Extends range to first cell containing the same formatting conditions.

.SpecialCells(xlCellTypeSameValidtion)

Extends range to first cell containing the same validation rules.

.SpecialCells(xlCellTypeVisible)

Extends range to all visible cells.

.EntireColumn

Extends range to entire column.

.EntireRow

Extends range to entire row.

start sidebar
Inside Out
How Excel Extends a Range

When searching for a cell that matches one of the special cell types listed in Table 8-1, Excel begins searching at the active cell. From there, it searches to the right and downward, performing a greedy search, selecting as many cells as needed to reach the farthest cell that meets the criteria. If no cells are found, Excel changes the search direction and switches to a lazy search, returning the first cell that matches.

Excel will continue searching to the right, looking upward instead of downward for a match. If no matching cells are found, Excel will then search to the left. Again, Excel searches downward first and then upward.

To figure out which cells Excel will select, remember the following rules:

  • Right first, left second.

  • Down first, up second.

  • Right-down is a greedy search; all others are lazy searches.

    click to expand

end sidebar



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net