Need macro to delete certain named ranges

tracycamp

New Member
Joined
Nov 17, 2014
Messages
20
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



Labor TypePersonnel Qualified to Perform Role to Left
Operator2BobBillabcdefgh
ElectricianSusiePerson E2Person E3ijklmno
PlumberPerson P1Person P2Person P3Person P4pqrstu
Maintenance_TechPerson MT1Person MT2Person MT3Person MT4Person MT5vwxyz
Facilities_MaintenancePerson FM1Person FM2Person FM3Person FM4Person FM5Person FM6Person FM7Person FM8aaab
MillwrightsPerson M1Person M2Person M3Person M4Person M5acadaeafag
WelderPerson W1Person W2Person W3Person W4ahaiajakalam
FabricatorPerson F1Person F2Person F3anaoapaqarasat
Operator_Cert_AOp Cert A 1Op Cert A 2auavawaxayazbabb
New_Other Other 2Other 3bcbdbebfbgbhbi

<colgroup><col><col span="6"><col><col><col span="2"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
tracycamp,

You might give this a try...

Code:
Sub DeleteNamedRanges()
Application.ScreenUpdating = False
    Dim nm As Name
    Dim rng1 As Range, rng2 As Range, isect As Range
    
    Set rng1 = Sheets(1).Range("D1:N11") ' Change sheet reference to suit
    For Each nm In ActiveWorkbook.Names
        Set rng2 = Range(nm)
        Set isect = Application.Intersect(rng1, rng2)
        If Not isect Is Nothing Then nm.Delete
    Next
Application.ScreenUpdating = True
End Sub

The code looks for an intersection between D1:N11 and the named range, and if found, deletes the named range. Be sure to change the sheet reference to match your specific worksheet.

Cheers,

tonyyy
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,758
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top