Intersect method (Excel)

The Intersect method is used to Returns a Range object that represents the rectangular intersection of two or more ranges. If one or more ranges from a different worksheet are specified, an error is returned.

Syntax:

expression.Intersect (Arg1Arg2Arg3Arg4Arg5Arg6Arg7Arg8Arg9Arg10Arg11Arg12Arg13Arg14Arg15Arg16Arg17Arg18Arg19Arg20Arg21Arg22Arg23Arg24Arg25Arg26Arg27Arg28Arg29Arg30)

expression represents a variable that represents an Application object in excel.

Parameters:

Name Required/Optional Data type Description
Arg1 Required Range The intersecting ranges. At least two Range objects must be specified.
Arg2 Required Range The intersecting ranges. At least two Range objects must be specified.
Arg3Arg30

 

Optional Variant An intersecting range.

Return value : Range

Example

The following example selects the intersection of two named ranges, rng1 and rng2, on Sheet1. If the ranges don’t intersect, the example displays a message.

Worksheets("Sheet1").Activate
Set iselect = Application.Intersect(Range("rng1"), Range("rng2"))
If iselect Is Nothing Then
MsgBox "Range does not intersect"
Else
iselect.Select
End If

The following example compares the Worksheet.Range property, the Application.Union method, and the Intersect method.

Range("A1:A10").Select                      'Selects cells A1 to A10.
Range(Range("A1"), Range("A10")).    'Select 'Selects cells A1 to A10.
Range("A1, A10").Select                    'Selects cells A1 and A10.
Union(Range("A1"), Range("A10")).Select     'Selects cells A1 and A10.
Range("A1:A5 A5:A10").Select                        'Selects cell A5.
Intersect(Range("A1:A5"), Range("A5:A10")).Select   'Selects cell A5.

References:

https://docs.microsoft.com/en-us/office/vba/api/excel.application.intersect