Delete all the names

[ChrisUK]

Hi,

I have a workbook with hundreds of names in it, how do I delete them all please? There must be an easy way!

TIA

Chris

 

[silent bob]

You need to be a bit more specific

 

[Brunts]

Sub DeleteAllNames()
Dim nName As Name
For Each nName In Names
nName.Delete
Next nName
End Sub

 

[Mark F]

Sub DeleteSelectedNames()
'========================================================================
' THIS DELETES ALL NAMES FROM WORKBOOK OR SHEET WITH AN X IN COLUMN C NEXT TO NAME SPECIFIED COL B
' NAMES ARE ON LIST ACTIVESHEET THAT WAS CREATED BY listnms CODE BELOW
'
'========================================================================
    Dim CurrentSheet As Worksheet
    Dim ws As Worksheet
    Dim nm As String
    '-------------------------------------------------
    On Error Resume Next    ' in case name does not exist
    Set CurrentSheet = ActiveSheet
    '- loop list
    For c = 1 To 1000
        If CurrentSheet.Cells(c, 3).Value = "X" Then
            nm = CurrentSheet.Cells(c, 1).Value
    '-----------------------------------------
    '- workbook level name
            ActiveWorkbook.Names(nm).Delete
    '-----------------------------------------
    '- worksheet level name
            For Each ws In Worksheets
                ws.Names(nm).Delete
            Next
    '-----------------------------------------
        End If
    Next c
    '-------------------------------------------------
    MsgBox ("Done")
End Sub
Sub listnms()
'========================================================================
' THIS CREATES A LIST OF NAMED RANGES
' ****MUST BE IN WORKBOOK OR PERSONAL****
'========================================================================
    Sheets.Add Before:=Sheets(1)
    n = ActiveSheet.Name
    Worksheets(n).Range("A1").ListNames
    Columns("A:A").ColumnWidth = 15
    Columns("B:B").ColumnWidth = 51
End Sub

Or

Sub RemoveNamesInWorkbook()
''========================================================================
'' THIS DELETES ALL NAMES FROM WORKBOOK APART FROM "Print_Area"
''========================================================================
    Const strKeepThisName As String * 10 = "Print_Area"
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        If Not nm.Name Like "*" & strKeepThisName Then nm.Delete
    Next
    Set nm = Nothing
End Sub