SpreadsheetGear The Performance Spreadsheet Component Company 
Subtract Method
See Also  Example
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

C# 
virtual 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 Vista, Windows XP, Windows Server 2008, Windows Server 2003, Windows 2000, Windows Me and Windows 98, including 32 bit and 64 bit editions where applicable. SpreadsheetGear for .NET 1.x requires the Microsoft .NET Framework 1.1 or .NET 2.0 (works with .NET 3.x). SpreadsheetGear for .NET 2007 requires the Microsoft .NET Framework 2.0 (works with .NET 3.x).

See Also