Create non-contiguous named ranges

Aspose.Cells Product Family
1

It would be nice to be able to create non-contiguous named ranges using a syntax similar to what Excel offers in VBA:


Range myRange = worksheet.Cells.CreateRange(“C155:C174,K155:V174,Z155:AK174,AO155:AZ174”);
2

Hi,

Thanks for your posting and using Aspose.Cells.

Please try the following code to create non-contiguous ranges.

C#


//Instantiating a Workbook object

Workbook workbook = new Workbook();


//Adding a Name for non sequenced range

int index = workbook.Worksheets.Names.Add(“NonSequencedRange”);


Name name = workbook.Worksheets.Names[index];


//Creating a non sequence range of cells

name.RefersTo = “=Sheet1!$A$1:$B$3,Sheet1!$E$5:$D$6”;


Range[] ranges = name.GetRanges();


3

Thanks for the reply. That works, but you end up with a Range array. Is there any way to copy that array elsewhere easily (that is, without having to manually deal with the gaps between each Range in the array)?

4

Hi,

Thanks for your posting and using Aspose.Cells.

Please explain your requirements with more detail. We think, there might be some workaround that we can help you with.

Do you want to copy/paste non-contiguous range from one position to another? This will be doable using Range.Copy() method.

5

Yes, the goal is to copy and paste (data only) a non-contiguous range. The problem is that the Name.GetRanges() method returns an array of ranges, so using Range.Copy for each Range in the array forces you to keep track manually of the row/col offset between them.

6

Hi,

Thanks for your posting and using Aspose.Cells.

I have looked into this issue and found, Microsoft Excel does not allow copy/paste non-contiguous range, so it is likely that Aspose.Cells will also not implement this feature.

However, I have logged this issue in our database for investigation and consideration. We will look into it and implement this feature if it is feasible or implementable. Once, there is some fix or other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43418 - Copy and paste (data only) a non-contiguous range

7

You’re right, it can’t be done in Excel right off the bat, but something close can be achieved in VBA, like this:


Sheets(“Sheet1”).Select
Range(“A12:B14,F12:R14,U12:AF14,AI12:AT14”).Select
Selection.Copy
Sheets(“Sheet2”).Select
Range(“F9”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
8

Hi Henri,


Thank you for providing more information regarding the feature request logged earlier as CELLSNET-43418. We have recorded a note for the product team’s review and currently waiting for their response. As soon as we receive more information in this regard, we will post here for your kind reference.
9

The issues you have found earlier (filed as CELLSNET-43418) have been fixed in Aspose.Cells for .NET v20.6. you may also get the version @ NuGet repos. here (NuGet Gallery | Aspose.Cells 20.6.0).This message was posted using Bugs notification tool by Amjad_Sahi

10

The issues you have found earlier (filed as CELLSNET-43418) have been fixed in Aspose.Cells for .NET v20.6. you may also get the version @ NuGet repos. here (NuGet Gallery | Aspose.Cells 20.6.0).This message was posted using Bugs notification tool by Amjad_Sahi