I know a similar issue was addressed a long time ago in this thread:
But I have a current issue that appears to be related.
Using Excel directly, you may select non-contiguous cells and create a named range by separating them with a comma as “$A$1:$I$1,$A$12:$I$21”
and naming the range as “Data1”
You end up with a range containing cells from the first row and cells from the twelfth through twenty-first rows.
Creating a chart and specifying “Data1” as the data series properly charts the two non-contiguous sets of cells on the same series.
Using Aspose.Cells 6.0.0.0 I do this:
Dim dataSheet as Worksheet
Dim DataRange As Range = dataSheet.Cells.CreateRange(“$A$1:$I$1,$A$12:$I$21”)
After saving the .xlsx file and opening it, the named range “Data1” exists but only includes cells “$A$12:$I$21"”
The first portion of the cells before the comma are not included in the range.
The purpose for creating these ranges is to generate multiple clustered bar charts each containing a portion of rows from a large data table. If the data has 23 rows, charting all 23 together on the same chart is not readable as the bars and labels are too close together. So I am trying to spread them out by a max of 10 rows per chart so one data table end up making three charts (two with 10 bar clusters and one with 3 bar clusters). I need to keep referencing back to the same top row containing the column headers for each chart but include a different set of 10 data rows each time.
I want to end with three ranges like:
Data1 = “$A$1:$I$1,$A$2:$I$11”
Data2 =“$A$1:$I$1,$A$12:$I$21”
Data3 =“$A$1:$I$1,$A$22:$I$24”
But I end up with these ranges in the .xlsx file:
Data1 =“$A$2:$I$11”
Data2 =“$A$12:$I$21”
Data3 =“$A$22:$I$24”
The last data column and the total number of rows in the source data (“I” and 23 in this case) are variable so I generate the strings for the range values dynamically each time this runs.
I would appreciate any suggestions if there is a better way to achieve this.
Thanks.