Delete Names In A Specific Worksheet

Hi All,

Here is a procedure to delete Names in a specific worksheet.

Code:

---

Sub DeleteNames(ByVal ShtName As String)
   
    Dim i          As Long
    Dim Sht        As Worksheet
    Dim nmCount    As Long
   
    On Error Resume Next
    Set Sht = ThisWorkbook.Worksheets(CStr(ShtName))
    If Err.Number <> 0 Then
        MsgBox "Worksheet '" & UCase$(ShtName) & "' doesn't exist", 16, "ExcelFox.com"
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
   
    nmCount = Sht.Names.Count
   
    If nmCount Then
        With Sht
            For i = 1 To nmCount
                .Names(i).Delete
            Next
        End With
        MsgBox "Done!", 64, "ExcelFox.com"
    Else
        MsgBox "There is no Name in worksheet '" & ShtName & "' to delete.", 64, "ExcelFox.com"
    End If
   
End Sub

---

Call like

Code:

---

Sub Test()
    DeleteNames "Sheet1"
End Sub

---

Enjoy !!