Hi there,

Is there an easy way of deleting all defined names in a tab? (other than
selecting Insert, Names, Define, selecting them individually and then
delete)

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

Public Sub DeleteNames(SheetName As String)
Dim ThisName As Name

For Each ThisName In ThisWorkbook.Names
On Error GoTo Skip
If ThisName.RefersTo Like "=" & SheetName & "!*" Then ThisName.Delete
Skip:
Next ThisName

End Sub

"Darin Kramer" wrote:



Hi there,

Is there an easy way of deleting all defined names in a tab? (other than
selecting Insert, Names, Define, selecting them individually and then
delete)

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

Do you mean

- all names in this workbook
- only names that refer to a range in this worksheet
- only names local to this worksheet?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"K Dales" wrote in message
...
Public Sub DeleteNames(SheetName As String)
Dim ThisName As Name

For Each ThisName In ThisWorkbook.Names
On Error GoTo Skip
If ThisName.RefersTo Like "=" & SheetName & "!*" Then ThisName.Delete
Skip:
Next ThisName

End Sub

"Darin Kramer" wrote:



Hi there,

Is there an easy way of deleting all defined names in a tab? (other than
selecting Insert, Names, Define, selecting them individually and then
delete)

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

Thanks - I have pasted it into VBA, but how do I get it to run? (I dont
know the difference between a PUblic Sub and just a normal Sub?) Also
Could I replace Sheetname with the sheet name to which I want all names
to be deleted?

Appreciate your help.

D

*** Sent via Developersdex http://www.developersdex.com ***

Hi Bob,

To answer your question, only names local to the worksheet.

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

Darin,

KDales' code will only delete names that refer toa range in this worksheet.
So you need

Public Sub DeleteNames(SheetName As String)
Dim ThisName As Name

For Each ThisName In ThisWorkbook.Names
On Error GoTo Skip
If ThisName.Name Like "=" & SheetName & "!*" Then
ThisName.Delete
End If
Skip:
Next ThisName

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Darin Kramer" wrote in message
...

Hi Bob,

To answer your question, only names local to the worksheet.

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

If this is a one time thing, I'd use Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager to clean things up.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It has lots of options--including showing only those names that are worksheet
level names.

Darin Kramer wrote:

Hi there,

Is there an easy way of deleting all defined names in a tab? (other than
selecting Insert, Names, Define, selecting them individually and then
delete)

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson