Delete nearly all defined names from the active worksheet only

Excel 2007

Need to delete nearly all defined names from the active worksheet only. Worksheets can be named anything.

These two worksheets must be excluded from having any names deleted:

.codename(“Sheet1”)
.name(“Summary”)

Also, I don’t want any ~system names deleted, so exclude:
(although Print_Area is the only one I’ve seen…)

*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria

I have been trying to get something like this working (from searching these and other forums):

Sub deleteinvalidnames()

Dim invalidname As name
Dim Ans As Integer
Dim Msg As String
 
For Each invalidname In ActiveWorkbook.Names
     If invalidname.RefersToRange.Worksheet.name = ActiveSheet And _
         Not invalidname.name Like "*Print_Area" And _
         ActiveSheet.CodeName <> "Sheet1" And _
         ActiveSheet.name <> "Summary" Then
              Msg = invalidname.name & " = " & invalidname.RefersToRange.Address
              Ans = MsgBox("Do you want to Delete/Rename: " & Msg, vbYesNo + vbInformation)
              If Ans = vbYes Then invalidname.Delete
     End If
  Next invalidname
 
‘Then run other macro to create new names (this has no issues)…
resetnames
 
End Sub

I’ve tried some mixes with:

Set ws = Activesheet
With ws
   For each invalidname in ws.names

etc. but nothing seems to work.

I don’t really need to cycle through with each name, but I came across that code first and it looked like a good thing to have in while troubleshooting…

http://www.mrexcel.com/forum/excel-q...heet-only.html

I would appreciate any advice as to how to proceed. Thank you.

Using the Name quality you get the sheet name and the range address of the particulart name so a macro like this could be used.

Option Explicit

Sub DelName()
Dim nm As Name

For Each nm In Names

If InStr(nm, "Sheet1") = 0 Then
    nm.Delete
End If

Next nm

End Sub

will delete all range names on all sheets in a workbook except for the range names on sheet1
Alf

Originally Posted by Alf

Using the Name quality you get the sheet name and the range address of the particulart name so a macro like this could be used.
...

will delete all range names on all sheets in a workbook except for the range names on sheet1
Alf

Thanks. I'll see if I can get that to work with ActiveSheet tomorrow.

What i really need help with is restricting the deletion of Names to the active worksheet (that can be named anything). I have a test for the two sheets i need to exclude, and should be able to avoid deleting Print_Area, etc. but i cannot get the Sub to restrict itself to the active sheet.