Delphi - Excel: determining non-contiguous range

Question

I have a data sheet, and I want to define a disjoint range that can be used, for example, to create a chart.

While the range is rectangular, it pretty easily defines the range.

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

This creates a rectangular range with an upper left corner in A1 and a lower right corner in B10 . The problem is that the data is not defined so that it can be selected using a single rectangle.

As an example, we have the following 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, for example, I want to create a chart for Time, Col1 and Col2, then the range will be just ASheet.Range['A1', 'C10'] . If I want to create a chart for Time, Col1, Col3 and Col4, it is not possible to create a range this way (since Col2 should not be included).

In VBA, you can create the desired range simply by .Range("A1:B10,D1:E10") . Unfortunately, it seems that there is no equivalent way to define a range in Delphi (Excel2010), in fact it only supports a range with the format .Range[topLeft, bottomRight] .

My question is: How to determine non-contiguous range in Delphi?

Answer 1

After hours of browsing the Internet, I came across the key part: Application.Union while reading through the documentation for the Range object for the nth time.

The solution is to use the Excel Union object method to combine two adjacent ranges to define a new range that may be non-adjacent.

Example:

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