Intersecting ranges

[JerryGiese]

Isn't there an object in VBA where you can enter two or more ranges and it will return the range that intersects those two ranges?

 

[Oorang]

Yes it is actually called intersect. If you would like to explicitly call it, then use Excel.Intersect:

Dim rngStuff As Excel.Range
Set rngStuff = Excel.Intersect(ActiveSheet.UsedRange, ActiveSheet.Cells(1, 2))

Note that if you prefer to use implicit code then you can just:

Dim rngStuff As Range
Set rngStuff = Intersect(ActiveSheet.UsedRange, ActiveSheet.Cells(1, 2))