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 !!