Delphi - Excel: Define a non-contiguous range

Question

What I have is a sheet with data and I want to define a non-contiguous range that can be used to, as an example, create a chart.

As long as the range is rectangular it's pretty easy defining the range.

range := ASheet.Range['A1', 'B10'];

This creates a rectangular range with the top left corner at A1 and bottom right corner in B10. The problem is when the data isn't defined so that it can be selected with a single rectangle.

As an example we have this data:

+-------+------+------+------+------+
| Time  | Col1 | Col2 | Col3 | Col4 |
+-------+------+------+------+------+
| 01:20 |    5 |    1 |  101 |   51 |
| 01:21 |    6 |    1 |  101 |   51 |
| 01:22 |    5 |    0 |  101 |   51 |
| 01:23 |    5 |    0 |  101 |   51 |
| 01:24 |    5 |    0 |  101 |   55 |
| 01:25 |    5 |    1 |  101 |   55 |
| 01:26 |    6 |    1 |  101 |   15 |
| 01:27 |    7 |    2 |  101 |   15 |
| 01:28 |    7 |    2 |  101 |   15 |
+-------+------+------+------+------+

If I for example wish to create a chart for Time, Col1 and Col2 then the range is simply ASheet.Range['A1', 'C10']. If I want to create the chart for Time, Col1, Col3 and Col4 it's not possible to create a range this way (since Col2 shouldn't be included).

In VBA it's possible to create the desired range by simply .Range("A1:B10,D1:E10"). Unfortunately there doesn't seem to be an equivalent way of defining a range in Delphi (Excel2010), in fact it seems to only support range with the format .Range[topLeft, bottomRight].

My question is: How do I define a non-contiguous range in Delphi?

Answer 1

After hours and hours of googling I stumbled upon the key part: Application.Union, when reading through the documentation for Range object for the nth time.

The solution is to use the Excel object method Union to make a union of two contiguous ranges in order to define a new range, which can be non-contiguous.

Example:

ExcelApp := TExcelApplication.Create(..);
...
Range1 := Sheet.Range['A1', 'B10'];
Range2 := Sheet.Range['D1', 'E10'];

Range  := ExcelApp.Union(Range1, Range2);