Delete Names In A Specific Worksheet

[Admin]

Hi All,

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

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

Sub Test()
    DeleteNames "Sheet1"
End Sub

Enjoy !!