Need macro to delete certain named ranges

[tracycamp]

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 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>

 

[tonyyy]

tracycamp,

You might give this a try...

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

 

[tracycamp]

Thanks. I'll give it a shot shortly and will let you know what happens.

T