Macro to delete Defined Names

[Mike Piazza]

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

 

[keepITcool]

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 :