Manipulating Groups of Cells

 < Day Day Up > 

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
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

start sidebar
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.

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

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

end sidebar

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



Numeric Value


Returns cells that contain errors



Returns cells that contain a logical (that is, Boolean) value



Returns cells that contain numerical values



Returns cells that contain text


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
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 & " " & _
strReturn = InputBox(strPrompt, strTitle)
rgeCurrent = CSng(strReturn)
Next rgeCurrent
End Sub

 < 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 © 2008-2017.
If you may any questions please contact us: