Cells, Ranges and Areas

Aspose.Cells Product Family
1

Hi there,

in Excel I can combine ranges and let Excel do the work of identifying the areas. This is the VBA Code for Excel:

Sub StoreCellList(byref oxlwsheet As Worksheet, ByRef c As Collection)
    Dim item As Range
    Dim r As Range

    If c.count > 0 Then
      For Each item In c
        If r Is Nothing Then
          Set r = item
         Else
          Set r = Union(r, item)
        End If
      Next item
      Dim r2 As Range
      For Each r2 In r.Areas
        debug.print oxlwsheet.name & "!" & r2.Address
      Next r2
    End If
End Sub

I tried to do the same with Aspose.cells but was not able to. Can you please provide an example for this problem? That would be great, thanks in advance.

Kind Regards
Norman

3

@norman.neubert
Please use WorksheetCollection.CreateUnionRange to create union range as the following codes:

  Workbook workbook = new Workbook();

  // Create union range
  UnionRange unionRange = workbook.Worksheets.CreateUnionRange("sheet1!A1:A10,sheet1!C1:C10", 0);
4

@norman.neubert
You can refer to the following sample code to achieve your goal:

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Cells cells = sheet.Cells;
// create range collection
Range r1 = cells.CreateRange("A1", "B2");
Range r2 = cells.CreateRange("A4", "B5");
Range r3 = cells.CreateRange("A7", "B8");
Range r4 = cells.CreateRange("A10", "B11");
RangeCollection ranges = cells.Ranges;
ranges.Add(r1);
ranges.Add(r2);
ranges.Add(r3);
ranges.Add(r4);

// create UnionRange
UnionRange unionRange = wb.Worksheets.CreateUnionRange("sheet1!A15:B16", 0);
foreach (Range temp in ranges)
{
    unionRange = unionRange.Union(temp.Address);
}
Console.WriteLine(unionRange.Ranges.Length);

foreach(Range range in unionRange.Ranges) 
{
    Console.WriteLine(range.Address);
}

Hope helps a bit.

5

Hi John, great solution, it works! Thank you very much! :slight_smile:

Kind regards
Norman

6

Thank you Simon.

7

@norman.neubert,

You are welcome. It is nice to know that the suggested APIs and code snippet helped you accomplish your task. Please feel free to write us back if you have further queries or comments, and we will be happy to assist you soon.