Page 199 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

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
Chapter 8
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
173