1

I have an excel template which is filled by added sheets that I receive from colleagues. Each sheet that I receive contains about 12 named ranges which do all have the same names. Each name begins with "flags".

I found another question on stackoverflow that gives a solution to delete name ranges with names that are not equal to given names: loop through names and delete those not matching specified pattern

I used this solution to create my own code. The names I want to delete (because they make my file awfully slow) all start with "flags". I therefore used the 

left(myname.namelocal, 5)

function to find each name in my workbook which starts with "flags" and have it deleted.

However, this code does not work. The names are not deleted when I execute the code.

And I do not understand why it does not work. I have tried a similar code on a small file to test the code. It worked fine.

The only difference is that the test file was not password protected. That is what I added to the code to make sure the code can delete the names. 

Sub deletenames()
'verwijderd alle naam-bereiken waarvan de naam begint met flags uit het verwerkingsbestand.

Dim ws As Worksheet
Dim myName As Name

For Each ws In ThisWorkbook.Worksheets

ws.Unprotect ("TM")

Next ws

For Each myName In ThisWorkbook.Names

If Left(myName.NameLocal, 5) = "flags" Then myName.Delete

Next

End Sub

what can I do to make this code work?

Community
  • 1
  • 1
DutchArjo
  • 319
  • 3
  • 8
  • 29

1 Answers1

0

The problem is most likely that Excel allows both sheet-specific and workbook-wide named ranges. Since you've mentioned that all sheets are copied together, I suspect that named ranges are associated with sheets.

Edit: The sheet-specific named ranges have a name Sheet!Range, and thus your check must consider the sheet prefix. A solution as in your comment to compare the name with Like is ok:

If myName.NameLocal Like "*!flags*" Then myName.Delete
MP24
  • 3,110
  • 21
  • 23
  • when I change my code and make ik to show a msgbox containing the name.local for each in the workbook, I get names like 'Worksheet name'!flags10_filterdatabase. Therefore I think I should change the code to find the work "flags" in the range name, instead of looking at the beginning. Can I do so by using "like"? – DutchArjo May 23 '14 at 08:01
  • 1
    this code did the trick for me: `If myName.NameLocal Like "*" & "flags" & "*" Then myName.Delete` – DutchArjo May 23 '14 at 08:07