Can't cycle through different ranges and delete same name.

[Ochimus]

Attached sheet has three DropLists (Cols D, H and L) containing Operator names, populated by the Ranges in Cols O - Q.
Same name can appear in more than one list.
When a name is selected, need to remove the name from ANY Range it appears in, so the same operator cannot be assigned twice.

Code below should loop through Col D, H and L. If it finds a name, it should loop through Cols O - Q looking for every instance of that name. When it finds a match, it adds it to an Array. When it finishes searching for a match to Col L the array should hold all instances of each name selected, which should then be deleted from Cols O - Q, and the remaining names 'shuffled up' to close the gaps, so none of the DropLists will now include any name selected already.

Can't see why it is isn't working?

Please Login or Register  to view this content.

All solutions, suggestions and alternatives welcome as ever.

Ochimus

[Mumps1]

Place this macro in the code module for the worksheet. The macro is based on the 3 instructions you listed in the "SCHEDULE" sheet. Since the macro is triggered automatically upon selection in D2, H2 or L2, the "Prevent Duplicates" button is no longer needed.

Please Login or Register  to view this content.

[Ochimus]

Mumps,

Many thanks for the prompt response, but unfortunately it isn't quite working?

The attached uses your Sheet Change trigger.

If you select Operator 3 as "Red", the Worksheet Change removes it from the Operator 2 list (Col P), but not from Operator 1 (Col O).
It also leaves it in the Operator 3 list itself (Col Q) - apologies if that step was not spelled out clearly enough in the original post.

To reiterate, if you select any name in D, H or L it needs to disappear completely from O - Q.

Ochimus

[Mumps1]

Try:

Please Login or Register  to view this content.

[Ochimus]

Mumps,

Looks to work perfectly!

Support much appreciated. Can now mark this as Solved and hope it can help many others in turn.

Just as a matter of interest, any idea why the "Union R1C1" approach I tried originally fell over?

Ochimus

Ochimus

[Mumps1]

You are very welcome. To be honest, I'm not sure why the "Union R1C1" approach didn't work for you.