How to get the intersection of two ranges in Excel

Spire.Xls.CellRange class provides a method named Intersect(CellRange range) that is used to find the intersection of certain ranges. This is very useful when we need to get the common value(s) of two ranges in an excel worksheet.

In below picture, we take range A2:C8 and range B2:D8 as an example. Cells filled in yellow color are the intersection of the two ranges.

Now refer to the following detail steps:

Step 1: Instantiate an object of Workbook class and load the Excel document.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Get the intersection of the two ranges and print the common values of them.

CellRange range = sheet.Range["A2:C8"].Intersect(sheet.Range["B2:D8"]);
foreach (CellRange r in range)
{
    Console.WriteLine(r.Value);
}

Output:

How to get the intersection of two ranges in Excel

Full code:

[C#]

using System;
using Spire.Xls;

namespace Get_the_instersection_of_two_ranges
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            CellRange range = sheet.Range["A2:C8"].Intersect(sheet.Range["B2:D8"]);
            foreach (CellRange r in range)
            {
                Console.WriteLine(r.Value);
            }
            Console.ReadKey();
        }
    }
}

[VB.NET]

Imports Spire.Xls

Namespace Get_the_instersection_of_two_ranges
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("Sample.xlsx")
			Dim sheet As Worksheet = workbook.Worksheets(0)
			Dim range As CellRange = sheet.Range("A2:C8").Intersect(sheet.Range("B2:D8"))
			For Each r As CellRange In range
				Console.WriteLine(r.Value)
			Next
			Console.ReadKey()
		End Sub
	End Class
End Namespace