We tend to think of a Range as a contiguous set of cells: a single cell, part (or all) of a column, part (or all) of a row, or a rectangular area. However, a Range is one or more or these. The contiguous areas of a Range are held in its Areas collection. If Areas.Count > 1 then we have a multi-area Range.
To construct a multi-area Range from several single-area ranges, you can use the Union operation, which is provided by the Application (so, effectively, global).
I came across an example of this in customizing a chart to show only the most recent N records in the source data. Here’s an accumulating table of monthly records:
I want a clustered column chart, with Months as the category (X) axis, and In/Out as the series.
If I wanted data from all records, I would set the chart’s source data range to be the entire table, including the headers, which Excel interprets to give the series names (that is, In and Out).
If I want my chart to show only the most recent 3 months (say), then I need to specify the source data range as the union of A1:C1 and A6:C8. Here’s the Source Data Range as it appears in the chart configuration:
=’Data 2010′!$A$1:$C$1,’Data 2010′!$A$6:$C$8
Here’s a procedure that constructs and sets the range for a given number of months:
Sub LastNMonths(nmonths As Integer)
'pre: 0 < nmonths
' <= Range("A1").CurrentRegion.Rows.Count - 1
Dim ws As Worksheet
Dim firstrow As Integer, lastrow As Integer
Set ws = Worksheets("Data 2010")
lastrow = ws.Range("A1").CurrentRegion.Rows.Count
firstrow = lastrow - nmonths + 1
Dim headers As Range
Set headers = ws.Range("A1:C1")
Dim rng As Range
Set rng = ws.Range("A" & firstrow & ":C" & lastrow)
Set rng = Union(headers, rng)
Dim cht As Chart
Set cht = Charts("Chart 2010")
With cht
.SetSourceData rng
.ChartTitle.Text = _
MonthsTitle(ws, firstrow, lastrow)
End With
End Sub
MonthsTitle simply constructs the chart title from the first and last months (from column A) – for example, “Report May-10 to Jul-10″.
As I did this initially in v2003, I have not made use of the ‘table-ness’ of the data – hence the Range(“A1″).CurrentRegion.
The procedure was called from a ‘Refresh Chart’ command button on a worksheet, with the nmonths value provided via an InputBox.
Before anyone points this out, let me say that you can do a certain amount of this kind of thing using formulas in the Chart configuration (using OFFSET, etc) – see Walkenbach’s ‘Formulas’ book. However, I think it’s clearer, and certainly more flexible to do it in VBA.
0 Responses to “Multi-Area Ranges”