Removing Named Ranges

Public Sub DeleteNamedRanges() 
Dim lcount As Long 
Dim sname As Name 
Dim smessage As String 
   For lcount = ActiveWorkbook.Names.Count To 1 Step -1 
      smessage = lcount 
         
      On Error Resume Next 
      Set sname = ActiveWorkbook.Names(lcount) 
      
      If (InStr(sname.RefersTo, "#REF") > 0) Then 
         smessage = sname.Name & " - deleted" 
         ActiveWorkbook.Names(lcount).Delete 
         Debug.Print smessage 
      Else 
         Debug.Print sname.Name 
      End If 

   Next lcount 
End Sub 

Workbook Specific

Public Sub DeleteNamedRanges() 
Dim sname as Name 
   For Each sname in ActiveWorkbook.Names 
      ActiveWorkbook.Names(sname.name).Delete 
   Next sname 
End Sub 

Identifying worksheet level named ranges

For Each objName In ActiveWorkbook.Names 
   If objName.Name = worksheet!namedrange 
Next objName 

Worksheet Specific

Public Sub DeleteNamedRanges() 
Dim inamecount As Integer 
   For inamecount = 1 To ActiveSheet.Names.Count 
      ActiveSheet.Names(1).Delete 
   Next inamecount 
End Sub