proper syntax for using variables in intersect(target, range(arg1,arg2...) method

Question

I have the following code working fine: If not intersect(target, range("a1:a50, b1:b50, c1:c50")) is nothing then

I want to replace the 3 ranges with variables but cant quite get the syntax correct.

it works fine with one variable: If not intersect(target, range(variable1)) is nothing then

When I try it with multiple variables I get an error if not intersect(target, range(variable1, variable2, variable3)) is nothing then

the error I get is "wrong number of arguments or invalid property assignment.

Can anyone help?

Thanks,

Dan

Answer 1

If Not Intersect(target, Range(variable1 & "," & variable2 & "," & variable3))

OR

If Not Intersect(Intersect(Intersect(target, Range(variable1)), Range(variable1)), Range(variable1))

Depending on how your ranges are defined.

Another thing you can do:

Dim myR As Range

variable1 = "A:C"

variable2 = "C:E"

variable3 = "1:2"

Set myR = Range(variable1)

Set myR = Union(myR, Range(variable2))

Set myR = Union(myR, Range(variable3))

MsgBox myR.Address

Bernie

Answer 2

Assuming your variables are Range names, and not defined as ranges themselves, you can do:

If Not (Intersect(Target, Union(Range(Variable1), Range(Variable2), Range(Variable3))) Is Nothing) Then