Manipulating Groups of Cells

---

One reason for using a range of cells is to manipulate all of the cells within the range together as one group rather than having to work with each cell individually. There are numerous things that can be done to a group of cells collectively, such as resizing them, adding borders, or changing the format of how the contents appear. You can even work with two groups of cells together to generate either the union of the two groups or find where the two groups intersect.

Resizing Cells Programmatically

The default size for a cell has a width of 8.43 points and a height of 12.73 points. These settings usually work just fine if you are working on a simple worksheet that's not going to be shared with anyone else. With a worksheet that is to be shared or that contains a good bit of data, however, you might need to resize some of your cells.

To begin with, it is not possible to resize one particular cell. Any changes in width will affect the entire column, and any changes in height will affect the entire row. Therefore, make any changes done within a macro using either the Width property of the Column object or the Height property of the Row object. You can set the Width and Height properties explicitly, if you have a predetermined size to use. You can also instruct Excel to calculate the proper size by using the AutoFit method.

Joining Two Ranges Together

It might not always be desirable to create a range of non-contiguous cells when defining ranges. You might need to keep the ranges separate for tracking purposes. In the Y2001ByMonth.xls workbook, the values are stored by date, but some analysis might need to be done based on the day of the week. Naturally, each individual weekday is separated from its kin by the other days of the week. We can pull all the same weekday ranges together using the Application object's Union method.

For more information on the Application object, see Chapter 6, 'The Application Object.'

The Union method takes two or more ranges and combines them into one range. The syntax is

 expression.Union(Arg1 as Range, Arg2 as Range, …)

• expression is an optional Application object.

• Arg1, Arg2, … are range objects of which at least two must be specified.

The following procedure calculates the average sales for each day of the week by hour:

Sub CalcWeeklyAverages()
Dim intDayOfWeek As Integer, intWeeks As Integer, intHours As Integer
Dim rgeWeek As Range, rgeMonth As Range, rgeDay As Range
Dim sglTotal As Single
Dim strRow As String, intWeek As Integer
 
    'Loop through each day of the week
    For intDayOfWeek = 1 To 7
    
        'Find first day of month
        Set rgeDay = Range("D6", Range("D6").End(xlDown))
        Set rgeMonth = Nothing
        intWeeks = 0
        
        'Loop through each week
        For intWeek = intDayOfWeek To _
            rgeDay.Cells(rgeDay.Cells.count, -1) Step 7
            
            'Calculate row number
            strRow = Format(intWeek + 5, "#0")
            Set rgeWeek = Range("D" & strRow & ":O" & strRow)
            If intWeek = intDayOfWeek Then 'Adjust for first week
                Set rgeMonth = rgeWeek
            Else
                Set rgeMonth = Union(rgeMonth, rgeWeek)
            End If
            
            'Count number of times weekday occurs
            intWeeks = intWeeks + 1
        Next intWeek
        
        'Calculate row number
        strRow = Format(intDayOfWeek + 41, "00")
        
        'Set title column
        With Range("C" & strRow)
            .Formula = rgeDay(intDayOfWeek, 0)
            .Interior.ColorIndex = 36
            .Font.Italic = True
        End With
        
        'Loop through each time period
        For intHours = 1 To 12
            sglTotal = 0
            For intWeek = 1 To intWeeks
                sglTotal = sglTotal + rgeMonth(intWeek, intHours)
            Next intWeek
            
            'Set average and format
            With Range(Chr$(Asc("C") + intHours) & strRow)
                .Formula = sglTotal / intWeeks
                .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00)"
            End With
        Next intHours
        
        'Add average total for weekday
        With rgeMonth(1, 1).Offset(36, 13)
            .Formula = "=Sum(R[0]C[-13]:R[0]C[-2])"
            .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00)"
        End With
    Next intDayOfWeek
    
End Sub 

Inside Out
Measuring the Dimensions of a Cell

Change the dimensions of a cell, either by adjusting the width or the height, by using the Format, Column, Width or the Format, Row, Height commands. Both commands display a dialog box that shows the current value and lets you enter a new value. What neither box explains is the unit of measurement being used to determine the size.

For row height, it's fairly simple. By default all rows are set to AutoFit. This setting means the row will increase in size to match either the largest-size font that's entered on that row or all the lines of a multi-line entry. Row height is measured in points (1 point = 1/72 inches), the same as font size. The AutoFit option will add a couple of points as padding to help ensure the entire text is visible.

Because there is no standard method of measuring width, Microsoft had to devise its own method. The width of a cell within Excel is done by calculating the average number of digits 0 through 9 that will fit in the cell using the standard font (usually 10 point Arial). Unfortunately, it is not an exact measurement. The number of digits that are visible will vary depending upon the Zoom level of the worksheet. Figure 8-4 shows a worksheet with the Zoom level set at 100%. Just about nine complete digits are visible, slightly more than the '8.43' default column width. Adjusting the Zoom level to 200%, as displayed in Figure 8-5, barely shows any features of the ninth digit, not even coming close to the '8.43' specified in the column width.

Figure 8-4: The number of digits visible at 100% Zoom…

Figure 8-5: …might not be the same as the number of digits visible at 200% Zoom

Detecting Empty Cells

An infamous saying among computer programmers is 'garbage in, garbage out.' What this means is that if bad data is fed into a procedure, it will generate a bad answer. To guard against this, the integrity of the data needs to be verified.

One element you should check before relying on the totals within the Y2001ByMonth.xls workbook is that all values have been entered. You can do so by searching for empty cells and prompting the user for a value to be entered into the cell. Searching for empty cells is accomplished by using the Range object's SpecialCells method. The syntax for the SpecialCells method is

 expression.SpecialCells(Type, Value)

expression is a required range object.

Type is a required value that should match one of the xlCellType constants, listed in Table 8-1.

Value is an optional variant. If Type is xlCellTypeConstants or xlCellTypeFormulas, Value is used to determine which type of cells to include in the result. The numeric values that correspond to the xlSpecialCellsValues constants, listed in Table 8-2, can be added together to return more than one type of special cell.

Table 8-2: xlSpecialCellsValue Constants

Constant	Description	Numeric Value
xlErrors	Returns cells that contain errors	16
xlLogical	Returns cells that contain a logical (that is, Boolean) value	4
xlNumbers	Returns cells that contain numerical values	1
xlTextValues	Returns cells that contain text	2

This FindEmptyCells procedure searches for empty cells in the data areas of Y2001ByMonth.xls and prompts the user for a value to enter into each empty cell found.

Sub FindEmptyCells()
Dim rgeSearch As Range, rgeEmpties As Range, rgeCurrent As Range
Dim strPrompt As String, strTitle As String, strReturn As String
Dim strRow As String, strAddress As String, strColumn As String
 
    strTitle = "Microsoft Office Excel 2003 Programming Inside Out"
    
    'Select proper range based on days in month
    Set rgeSearch = Range("D6", Range("D6").End(xlDown))
    Set rgeSearch = Range(rgeSearch, rgeSearch.End(xlToRight))
    
    'Find Empties - End if none found
    'Set Error trap for no cells found
    On Error Resume Next
    Set rgeEmpties = rgeSearch.SpecialCells(xlCellTypeBlanks)
    If Err.Number = 1004 Then
        strPrompt = "No empty cells found!"
        MsgBox strPrompt, vbOKOnly + vbInformation, strTitle
        Exit Sub
    Else
        strPrompt = "Unexpected error - " & Str$(Err.Number) & _
            vbCrLf & Err.Description
        MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle
    End If
    
    'Reset error handler
    On Error GoTo 0
    
    'Loop through empties prompting for new values
    For Each rgeCurrent In rgeEmpties
        
        'Calculate time period
        strAddress = "R5C" & Format(rgeCurrent.Column, "#0")
        strAddress = Application.ConvertFormula(strAddress, xlR1C1, xlA1)
        strPrompt = "Value missing for " & Format(Range(strAddress), _
            "h:mm AM/PM")
            
        'Calculate day
        strAddress = "R" & Format(rgeCurrent.Row, "#0") & "C2"
        strAddress = Application.ConvertFormula(strAddress, xlR1C1, xlA1)
        strPrompt = strPrompt & " on " & ActiveSheet.Name & " " & _
            Range(strAddress)
        strReturn = InputBox(strPrompt, strTitle)
        rgeCurrent = CSng(strReturn)
    Next rgeCurrent
End Sub

---