Union of Named Ranges

Having some issues. Simply want to combine 2 named ranges into one on a cell change event. Cell change event is no problem. I can't get the union to work. I have this code on sheet 3. What am I doing wrong?

Please Login or Register  to view this content.

Getting a 1004 error. "Method 'Range' of object '_Worksheet' failed" on this line

Please Login or Register  to view this content.

Should I have this in a worksheet or in a seperate Module?

Removing the square brackets didn't work. I had it like that before, but I thought I would try them.

Any ideas why your code won't work in my spreadsheet shg?

Any one else worked on something like this in the past? Is there an easier way to get 2 dynamic ranges into a drop down validation cell? I'm just sitting scratching my head now.

My actual workbook I won't post here as it is becoming huge and I don't want to confuse anyone. Here is basically what I'm trying to do. I used the same code and getting the same errors.

Thanks for the help everyone

union trial.xlsm

mikerickson, having trouble using the resulting named range in a data validation cell as that has a maximum of 1 column allowed. Hence why I was attempted to use the union. But I didn't fully understand the way the named ranges worked until now.

*face palm* Of course. Thank you Andy. I'll rework this sheet to create a combined list on a hidden sheet and feed the data validation from there. Much easier solution.