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 (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)
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. |
| Arg3–Arg30
|
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