### Multi-Area Ranges

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 rng As Range
Set rng = ws.Range("A" & firstrow & ":C" & lastrow)

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.

#### 2 Responses to “Multi-Area Ranges”

1. February 11, 2014 at 7:13 pm

Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates.
I’ve been looking for a plug-in like this for quite some time
and was hoping maybe you would have some experience with something like this.
Please let me know if you run into anything. I truly enjoy reading your