Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete Defined Names
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete defined names | Excel Discussion (Misc queries) | |||
unable to delete Macro names | Excel Discussion (Misc queries) | |||
How to delete all defined names from a workbook? | Excel Worksheet Functions | |||
How to delete all defined names from a workbook? | Links and Linking in Excel | |||
Macro to Finded Defined Names with #REF | Excel Programming |