Free Support Forum -

Named range containing non-contiguous cells not created correctly

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 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.


Well, if you need to create non contiguous named ranges for chart’s data series, you may use Name object, see the topic for your reference:

Also, here is a sample code for your reference.
Sample code:
Dim _WorkBook As New Workbook()
Dim worksheet As Worksheet = _WorkBook.Worksheets(0)
worksheet.Name = “Series”
worksheet.Cells(0, 0).PutValue(“Anne”)
worksheet.Cells(0, 1).PutValue(“Srie A”)
worksheet.Cells(0, 2).PutValue(“Srie B”)
worksheet.Cells(1, 0).PutValue(“2006”)
worksheet.Cells(1, 1).PutValue(54)
worksheet.Cells(1, 2).PutValue(46)
worksheet.Cells(2, 0).PutValue(“2007”)
worksheet.Cells(2, 1).PutValue(15)
worksheet.Cells(2, 2).PutValue(85)
worksheet.Cells(3, 0).PutValue(“2008”)
worksheet.Cells(3, 1).PutValue(70)
worksheet.Cells(3, 2).PutValue(30)

Dim index As Integer = _WorkBook.Worksheets.Names.Add(“Data1”)
Dim name As Name = _WorkBook.Worksheets.Names(index)
'Creating a non sequence range of cells
name.RefersTo = “=Series!$B$2:$B$4,Series!$C$2:$C$4”

Dim chartsheet As Worksheet = _WorkBook.Worksheets(1)
chartsheet.Name = “Chart”
chartsheet.Charts.Add(ChartType.Column100PercentStacked, 0, 0, 25, 10)
Dim chart As Chart = chartsheet.Charts(0)
chart.PlotArea.Area.ForegroundColor = Color.Transparent
chart.Legend.Position = LegendPositionType.Bottom

chart.NSeries(0).XValues = “Series!A2:A4”


Thank you.

This method throws an invalid formula exception when I pass a multi-part cell reference containing a single quoted sheet name value with spaces.

This works (spaces with single quotes and no comma):
categoryName.RefersTo = ‘File Size Per Media’!A3:A11

This executes in code (spaces without single quotes) but the resulting .xlsx file is corrupt and will not open.:
dataName.RefersTo = File Size Per Media!$A$2:$I$2,File Size Per Media!$A$3:$I$12

This throws exception (spaces with single quotes plus comma):
dataName.RefersTo = ‘File Size Per Media’!$A$2:$I$2,‘File Size Per Media’!$A$3:$I$12

This throws exception (no spaces with single quotes plus comma):
dataName.RefersTo = ‘FileSizePerCustodian’!$A$2:$I$2,‘FileSizePerCustodian’!$A$13:$I$22

Using R1C1 notation, this works to create the name reference and NSeries:
categoryName.R1C1RefersTo = "R3C1:R12C1"
chart.NSeries.AddR1C1(dataName.R1C1RefersTo, False)

I do end up with absolute references to the cells and the code executes.

The properties of the series appear correct:
chart.NSeries(0).Values property shows:
(‘File Size Per Media Chart 1’!$A$2:$I$1,‘File Size Per Media Chart 1’!$B$3:$I$12)

But the .xlsx file is corrupt and I get an auto recovery dialogue when opening in Excel. If I allow Excel to “fix” the issue it deletes the named range Aspose created saying "Removed Records: Named range from /xl/workbook.xml part (Workbook)"

There appear to be some conflicts within these methods.

I need to use the sheet names in the cell references as the data will be on a different sheet from the chart(s).


Well, it works similar to MS Excel. If a worksheet has spaces in its name, you have to enclose it within single quotes. This is same as Excel.

Could you create a sample console application to show the issue, we will check it soon.

Thank you.

I have found a solution for building these charts while writing you a test app to show the problem. By defining each column of each NSeries explicitly, I am able to chart the non-contiguous data cells. I also am not using any Name objects at all.

The code samples I have seen show defining the entire range of cells to be charted such as

chart.NSeries.Add("‘The Data’!$B$2:$C$3",True)

But I am only able to get this to work by separately defining each column of data

chart.NSeries.Add("‘The Data’!$B$2:$B$3",True)
chart.NSeries.Add("‘The Data’!$C$2:$C$3",True)

and then specifying the same category data for every series

chart.NSeries.CategoryData = "‘The Elements’!$A$3:$A$6"

Attached is the vb module for a console app doing what I need. If you see a better way of achieving this please let me know. But at this point, my code is doing what I need.