Subtract Method (IRange)

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

Syntax

Visual Basic
C#
Delphi
JScript
Managed Extensions for C++
C++/CLI

'Declaration
 
Function Subtract( _
   ByVal range2 As IRange _
) As IRange

'Usage
 
Dim instance As IRange
Dim range2 As IRange
Dim value As IRange
 
value = instance.Subtract(range2)

IRange Subtract( 
   IRange range2
)

function Subtract( 
    range2: IRange
): IRange;

function Subtract( 
   range2 : IRange
) : IRange;

IRange* Subtract( 
   IRange* range2
)

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

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 Basic

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

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

IRange Interface
IRange Members
Intersect Method
Union Method
Offset Method