Excel VBA Union method

Question

Two of the worksheet-defined ranges in my spreadsheet are manipulated by macros that can either extend (as new entries are added) or contract (as entries are removed) the ranges. It is highly convenient to maintain these as separate ranges.

However, in the spreadsheet itself I also have a need to use the data validation function to refer to a list of names that is a combination of the two ranges. I assume the Union method is the way to do this. I have tried inserting the code below into the macro that adds an entry to the list of payees, but I get a syntax error. I am probably committing some elementary error. This is the statement in VBA:

   Set Range("namelist") = Application.Union(Range("payerlist"), Range("payeelist"))

VBA says that the first term - Range("namelist") - is an invalid use of property. Can someone please put me straight? (There may be other problems in what I have written, of course.)

Answer 1

Hi

I'm not sure what you're trying to do, but this works to define a new range.

Sub testUnion()

 Application.Union(Range("payerlist"), Range("payeelist")).Select

Selection.Name = "namelist"

Range("namelist").Select

End Sub

Answer 2

Hi,

just a note,

you can't set defined name, two sections from two different sheets.

Answer 3

Thank you, John.

I have tried this in a test spreadsheet and I can use the united range as a parameter to the data validation facility, provided the two constituent ranges are in the same column, which they can be.

You will have realised that my gradually-acquired understanding of Visual Basic has some significant gaps.

Regards, Ron

Answer 4

Hello, All three ranges mentioned had already been defined in the worksheet.

Regards, Ron

Answer 5

Hi,

Did you define a range "namelist"?