Intersect, Target With Multiple Ranges

[dbmathis]

Hi All,

How should I alter this:

If Intersect(Target, Range("C14:D59")) Is Nothing Then Exit Sub

to work for multiple ranges?

 

[wigi]

I would use Union to for the multiple ranges you are writing about. Then, the Insersect can just be specified on Target (as now) and the union of the others. This will probably the most clean way of writing the code.

 

[dbmathis]

That worked. Thanks

 

[Boller]

I would use Union to for the multiple ranges you are writing about. Then, the Insersect can just be specified on Target (as now) and the union of the others. This will probably the most clean way of writing the code.

Why do you need to use the Union method?

 

[Rick Rothstein]

How should I alter this:

If Intersect(Target, Range("C14:D59")) Is Nothing Then Exit Sub

to work for multiple ranges?

Just use a comma separated list inside the Range call. For example, here is a 3-area test using this method.....

If Intersect(Target, Range("A3:C5,D4:F4,H6:H9")) Is Nothing Then Exit Sub

 

[wigi]

Why do you need to use the Union method?

Hello

As I indicated, that is probably the most clean way to write it. I did not say that you have to use it.

It might be more convenient (comparing with the solution by Rick) because I think it can be read easier. It will also allow for some loops (for instance, every other column could be easily done in a loop) and a Union, rather than a hardcoded list.

With Union we could add some "error handling", look at this topic of yesterday:
http://www.mrexcel.com/forum/showpost.php?p=2831125

I am not sure, but maybe there is a limit to the string length in Rick's code example - what is in between the Range(). Again, not sure of that.

 

[Rick Rothstein]

I am not sure, but maybe there is a limit to the string length in Rick's code example - what is in between the Range(). Again, not sure of that.

I am doing this from memory (not always a good thing to do), but I think there is a 255 character limit for the text string argument to the Range property call.

 

[Boller]

... the most clean way to write it .... I think it can be read easier ..... every other column could be easily done in a loop ......
With Union we could add some "error handling"

Can't think why you say it's cleaner(?) to write and easier to read.

Also, building a range via looping does not have to be done using Union, nor does error handling depend upon using Union.