Solved: Removing Defined Names in Worksheet

[Opv]

How does one delete all defined names for ranges within only one designated worksheet in a workbook, and at the same time retain the names for the named ranges in all other worksheets in the same workbook?

[Bob Phillips]

ARe they local to that sheet, or just refer to that sheet?

[Opv]

ARe they local to that sheet, or just refer to that sheet?
I can't avoid showing my ignorance here as I don't know what distinguishes the two. I have a small loop which automatically creates defined names to the headings in Row 1 of a particular worksheet. Over time I have changed the heading names and, consequently, the loop recreates the names based on the new values in Row 1. However, the old names are still showing up in the list of defined names. What I was thinking about doing is expand my loop to first delete all names associated with the headings in Row 1 and then recreate them using the current values. My current loop is as follows:


Sub defineNames()

Dim cel As Range
Dim rng As Range

With Sheets(1)
Set rng = .Range("A1", .Range("A1").End(xlToRight))

For Each cel In rng
cel.Name = cel.value
Next cel

End With

End Sub


This routine is called when the Workbook is opened.

[mikerickson]

Dim oneName as Name
Dim oneNamedRange as Range

For Each oneName in ThisWorkbook.Names
Set oneNamedRange
On Error Resume Next
Set oneNamedRange = oneName.RefersToRange
On Error Goto 0

If Not oneNamedRange Is Nothing Then
If oneNamedRange.Parent.Name = "Sheet1" Then
oneName.Delete
End If
End If
Next oneName

[Opv]

Dim oneName as Name
Dim oneNamedRange as Range

For Each oneName in ThisWorkbook.Names
Set oneNamedRange
On Error Resume Next
Set oneNamedRange = oneName.RefersToRange
On Error Goto 0

If Not oneNamedRange Is Nothing Then
If oneNamedRange.Parent.Name = "Sheet1" Then
oneName.Delete
End If
End If
Next oneName
Thanks. I'm receiving a Syntax compile error on the following row:


Set oneNamedRange

[Opv]

Thanks. I'm receiving a Syntax compile error on the following row:


Set oneNamedRange


The code seems to work fine with the above line commented out.

[mikerickson]

If there are names that don't refer to ranges (e.g. RefersTo: =Sheet1!$A$1 & " cat") the lineSet oneNamedRange = Nothing is needed there.

Oops.

[Paul_Hossler]

try


Set oneNamedRange = Nothing


Paul

[Opv]

try


Set oneNamedRange = Nothing

Paul

Works like a charm. Thanks.

[Opv]

If there are names that don't refer to ranges (e.g. RefersTo: =Sheet1!$A$1 & " cat") the lineSet oneNamedRange = Nothing is needed there.

Oops.

Thanks. I've made that change and it seems to be working fine.