Macro to delete Defined Names

I have have the below as a start on my why to writing a macro to delete
unused defined names from a workbook. MY question is can a place an if
statement where the [New Code] placeholder is to test if the name is
currently used in the workbook or refers to a print area? Thanks for the
help.

Sub DeleteNames()

'
' DeleteNames Macro
' Macro Written 5/12/2005 by MRP
'

Dim rng As Range
Dim ThisName As Name
Msg = "This Macro will delete all Defined Names in this workbook. Are
you sure you wish to proceed?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then

For Each ThisName In ActiveWorkbook.Names
[New Code]
ThisName.Delete
Next ThisName

End If

End Sub

Finding out if a name is used is not easy..
it may be used in vbacode, datavalidation, conditional formatting etc.

Jan Karel Pieterse has 2 excellent utilities that'll help you do it..
NameManager & FlexFind
download from http://www.oaltd.co.uk/MVP/Default.htm

other hint:
when you want to delete all items in a collection
it's better to use a reverse numeric iteration.
then a for each object in collection approach.


Best try:
With activeworkbook.names
For i = .Count To 1 Step -1
.Item(i).Delete
Next
end with

NOTE:
due to duplication of global and local names deleting
names by name is NOT that straightforward:
global names (workbook is parent) cannot be accessed if a local name
(same name with worksheet as parent) exists on the active sheet


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mike Piazza wrote :

> I have have the below as a start on my why to writing a macro to
> delete unused defined names from a workbook. MY question is can a
> place an if statement where the [New Code] placeholder is to test if
> the name is currently used in the workbook or refers to a print area?
> Thanks for the help.
>
> Sub DeleteNames()
>
> '
> ' DeleteNames Macro
> ' Macro Written 5/12/2005 by MRP
> '
>
> Dim rng As Range
> Dim ThisName As Name
> Msg = "This Macro will delete all Defined Names in this workbook.
> Are you sure you wish to proceed?"
> Ans = MsgBox(Msg, vbYesNo)
> If Ans = vbYes Then
>
> For Each ThisName In ActiveWorkbook.Names
> [New Code]
> ThisName.Delete
> Next ThisName
>
> End If
>
> End Sub