Application.Intersect method (Excel)

Article
03/30/2022

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 A variable that represents an Application object.

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, rg1 and rg2, on Sheet1. If the ranges don't intersect, the example displays a message.

Worksheets("Sheet1").Activate 
Set isect = Application.Intersect(Range("rg1"), Range("rg2")) 
If isect Is Nothing Then 
 MsgBox "Ranges don't intersect" 
Else 
 isect.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.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Share via