Find Range Function

I have used this so much I cannot do without it - it is a quick method to find all the ranges within a range than containing a value. The function is

Please Login or Register  to view this content.

This can be called with

Please Login or Register  to view this content.

Then simple matter of iterating through myRange to get all the ranges with the value. Could not live without this great function. Very robust.

You can assign default values to the arguments in the signature:

Please Login or Register  to view this content.

In addition, and notwithstanding that it appears as an example in Help, this line makes no sense:

Please Login or Register  to view this content.

In the code here, c cannot be Nothing (that's already been tested), and if it were, c.Address would generate a runtime error.

I've been using a similar loop but didn't know about the UNION trick...that kicks a$$

@shg you are dead right - my bad - it should be

Please Login or Register  to view this content.