The table at the bottom contains the data and I will try to describe the issue in detail.
I have a column called labor type that can have up to 10 different labor type entries and to the right of each entry is space for up to 10 names for each labor type. The user can edit the names and the labor types. After they make any changes, there is a macro that defines each row of 10 entries as a named range with the labor type (Welder, Plumber, etc.) as the name. I do this so that I can use dropdowns with the indirect function referencing the named range.
If they change the names, there is no problem but if they change the labor type, I end up with both the new and the old labor types as named ranges after the macro runs. This causes problems with some of the data validation that is used on other tabs.
So, I would like the macro to delete the old named ranges. I've found code to delete all named ranges, all with errors, etc. but I have many other named ranges in the workbook and on the same sheet that I don't want deleted. What I want is for the code to delete named ranges only if the named ranges are within D1:N11.
All help is appreciated.
Tracy
<colgroup><col><col span="6"><col><col><col span="2"></colgroup><tbody>
</tbody>
I have a column called labor type that can have up to 10 different labor type entries and to the right of each entry is space for up to 10 names for each labor type. The user can edit the names and the labor types. After they make any changes, there is a macro that defines each row of 10 entries as a named range with the labor type (Welder, Plumber, etc.) as the name. I do this so that I can use dropdowns with the indirect function referencing the named range.
If they change the names, there is no problem but if they change the labor type, I end up with both the new and the old labor types as named ranges after the macro runs. This causes problems with some of the data validation that is used on other tabs.
So, I would like the macro to delete the old named ranges. I've found code to delete all named ranges, all with errors, etc. but I have many other named ranges in the workbook and on the same sheet that I don't want deleted. What I want is for the code to delete named ranges only if the named ranges are within D1:N11.
All help is appreciated.
Tracy
Labor Type | Personnel Qualified to Perform Role to Left | |||||||||
Operator2 | Bob | Bill | a | b | c | d | e | f | g | h |
Electrician | Susie | Person E2 | Person E3 | i | j | k | l | m | n | o |
Plumber | Person P1 | Person P2 | Person P3 | Person P4 | p | q | r | s | t | u |
Maintenance_Tech | Person MT1 | Person MT2 | Person MT3 | Person MT4 | Person MT5 | v | w | x | y | z |
Facilities_Maintenance | Person FM1 | Person FM2 | Person FM3 | Person FM4 | Person FM5 | Person FM6 | Person FM7 | Person FM8 | aa | ab |
Millwrights | Person M1 | Person M2 | Person M3 | Person M4 | Person M5 | ac | ad | ae | af | ag |
Welder | Person W1 | Person W2 | Person W3 | Person W4 | ah | ai | aj | ak | al | am |
Fabricator | Person F1 | Person F2 | Person F3 | an | ao | ap | aq | ar | as | at |
Operator_Cert_A | Op Cert A 1 | Op Cert A 2 | au | av | aw | ax | ay | az | ba | bb |
New_Other | Other 2 | Other 3 | bc | bd | be | bf | bg | bh | bi |
<colgroup><col><col span="6"><col><col><col span="2"></colgroup><tbody>
</tbody>