SpreadsheetGear
Subtract Method
See Also  Example Send Feedback
SpreadsheetGear Namespace > IRange Interface : Subtract Method

range2
Specifies the range to subtract from this range.
Returns a new range which consists of this range with range2 removed, or null if the resulting range is empty.

Syntax

Visual Basic (Declaration) 
Function Subtract( _
   ByVal range2 As IRange _
) As IRange
C# 
IRange Subtract( 
   IRange range2
)

Parameters

range2
Specifies the range to subtract from this range.

Return Value

A new range consisting of this range with range2 removed, or null if the resulting range is empty.

Example

C#Copy Code
public void RangeIntersectUnionSubtract()
{
   // Create a workbook to play with.
   IWorkbook workbook = Factory.GetWorkbook();
   IWorksheet worksheet = workbook.Worksheets[0];
   IRange cells = worksheet.Cells;

   // Create some ranges.
   IRange a1 = cells["A1"];
   IRange a1_b3 = cells["A1:B3"];
   IRange b3_d4 = cells["B3:D4"];

   // A variable to hold results.
   IRange range;

   // A1 does not intersect with B3:D4
   range = a1.Intersect(b3_d4);
   Debug.Assert(range == null);

   // Subtracting B3:D4 from A1 does not change A1.
   range = a1.Subtract(b3_d4);
   Debug.Assert(range != null && range.Address.Equals("$A$1"));

   // The union of A1 and B3:D4 is "A1,B3:D4".
   range = a1.Union(b3_d4);
   Debug.Assert(range != null && range.Address.Equals("$A$1,$B$3:$D$4"));

   // B3:D4 does not intersect with A1.
   range = b3_d4.Intersect(a1);
   Debug.Assert(range == null);

   // Subtracting A1 from B3:D4 does not change B3:D4.
   range = b3_d4.Subtract(a1);
   Debug.Assert(range != null && range.Address.Equals("$B$3:$D$4"));

   // The union of B3:D4 and A1 is "A1,B3:D4".
   range = b3_d4.Union(a1);
   Debug.Assert(range != null && range.Address.Equals("$A$1,$B$3:$D$4"));

   // The intersection of B3:D4 and A1:B3 is B3
   range = b3_d4.Intersect(a1_b3);
   Debug.Assert(range != null && range.Address.Equals("$B$3"));

   // Subtracting A1:B3 from B3:D4 leaves "C3:D3,B4:D4".
   range = b3_d4.Subtract(a1_b3);
   Debug.Assert(range != null && range.Address.Equals("$C$3:$D$3,$B$4:$D$4"));

   // The union of B3:D4 and A1:B3 is "A1:B2,A3:D3,B4:D4".
   range = b3_d4.Union(a1_b3);
   Debug.Assert(range != null && range.Address.Equals("$A$1:$B$2,$A$3:$D$3,$B$4:$D$4"));

   // The intersection of "A1,B1,A2,B2" and "A1:B1,A2:B2" is A1:B2.
   range = cells["A1,B1,A2,B2"].Intersect(cells["A1:B1,A2:B2"]);
   Debug.Assert(range != null && range.Address.Equals("$A$1:$B$2"));
}
    
Visual BasicCopy Code
Public Sub RangeIntersectUnionSubtract()
    ' Create a workbook to play with.
    Dim workbook As IWorkbook = Factory.GetWorkbook()
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
    Dim cells As IRange = worksheet.Cells

    ' Create some ranges.
    Dim a1 As IRange = cells("A1")
    Dim a1_b3 As IRange = cells("A1:B3")
    Dim b3_d4 As IRange = cells("B3:D4")

    ' A variable to hold results.
    Dim range As IRange

    ' A1 does not intersect with B3:D4
    range = a1.Intersect(b3_d4)
    Debug.Assert(range Is Nothing)

    ' Subtracting B3:D4 from A1 does not change A1.
    range = a1.Subtract(b3_d4)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$A$1"))

    ' The union of A1 and B3:D4 is "A1,B3:D4".
    range = a1.Union(b3_d4)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$A$1,$B$3:$D$4"))

    ' B3:D4 does not intersect with A1.
    range = b3_d4.Intersect(a1)
    Debug.Assert(range Is Nothing)

    ' Subtracting A1 from B3:D4 does not change B3:D4.
    range = b3_d4.Subtract(a1)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$B$3:$D$4"))

    ' The union of B3:D4 and A1 is "A1,B3:D4".
    range = b3_d4.Union(a1)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$A$1,$B$3:$D$4"))

    ' The intersection of B3:D4 and A1:B3 is B3
    range = b3_d4.Intersect(a1_b3)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$B$3"))

    ' Subtracting A1:B3 from B3:D4 leaves "C3:D3,B4:D4".
    range = b3_d4.Subtract(a1_b3)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$C$3:$D$3,$B$4:$D$4"))

    ' The union of B3:D4 and A1:B3 is "A1:B2,A3:D3,B4:D4".
    range = b3_d4.Union(a1_b3)
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$A$1:$B$2,$A$3:$D$3,$B$4:$D$4"))

    ' The intersection of "A1,B1,A2,B2" and "A1:B1,A2:B2" is A1:B2.
    range = cells("A1,B1,A2,B2").Intersect(cells("A1:B1,A2:B2"))
    Debug.Assert(Not (range Is Nothing) And range.Address.Equals("$A$1:$B$2"))
End Sub

Requirements

Platforms: Windows 2000, Windows XP, Windows Vista, Windows Server 2003 and Windows Server 2008. SpreadsheetGear 2008 requires the Microsoft .NET Framework 2.0 and supports .NET 3.0 and .NET 3.5.

See Also

Copyright © 2003-2009 SpreadsheetGear LLC. All Rights Reserved.Help Powered by Innovasys   
SpreadsheetGear is a registered trademark of SpreadsheetGear LLC.
Microsoft, Microsoft Excel and Visual Studio are trademarks or registered trademarks of Microsoft Corporation.