Deleting Named Ranges error

[e17nel]

I have a macro that defines named ranges. Then I have a macro that clears the named ranges. This code has worked for me for a few years now in Excel 2007 and more recently 2010. I created a new workbook that has these macros in them and it worked fine. However, today, I started getting the "Run-time error '1004': The name that you entered is not valid.

Reasons for this can include:
-The name does not begin with a letter or an underscore
-The name contains a space or other invalid characters
-The name conflicts with an Excel built-in name or the name of another object in the workbook"

I am able to create the named ranges fine. I only get this error when trying to delete the named ranges with my macro. I looked in the Name Manager and none of the names have an error. I have changed the workbook to R1C1 and it didn't tell me that there was an error with my names. I am able, however, to go into the Name Manager and manually delete the named ranges without any problem. I can't manually delete the named ranges every time because this is a template used by co-workers and the number of named ranges varies. I don't have any objects (Pivot Tables, charts, etc.) in the workbook.

My code to delete:
Dim nm As name
For Each nm In ActiveWorkbook.Names
nm.Delete <-- Code breaks here
Next nm

My named ranges:
ActualAirtime1
ActualAllocated1
ActualData1
ActualKBAirtime1
ActualKBAllo1
ActualMSC1
ActualText1
ActualVoice1
CorrectAirtime1
CorrectData1
CorrectKB1
CorrectMSC1
CorrectText1
Credits1
Debits1

I am at a loss for what to do next. Any help is appreciated.

Thanks,
Eric

 

[hiker95]

e17nel,


Both versions of the below macros seemed to work correctly in a workbook with two worksheets, and two named ranges in each worksheet.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
  
  On Error Resume Next
  nm.Delete
  On Error GoTo 0
  
Next nm
End Sub



Sub TestV2()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
  
  Application.DisplayAlerts = False
  nm.Delete
  Application.DisplayAlerts = True
  
Next nm
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

 

[xenou]

Rather simplistic but try:
1) Reboot
2) Delete the code and paste it back in.

ξ

 

[hiker95]

e17nel,

It is a little unclear as to what you are actually trying to do.

If your template already contains the following range names:
ActualAirtime1
ActualAllocated1
ActualData1
ActualKBAirtime1
ActualKBAllo1
ActualMSC1
ActualText1
ActualVoice1
CorrectAirtime1
CorrectData1
CorrectKB1
CorrectMSC1
CorrectText1
Credits1
Debits1


And, you want to delete only those range names from the workbook, then try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub DeleteMyRangeNames()
' hiker95, 01/03/2013
' http://www.mrexcel.com/forum/excel-questions/677351-deleting-named-ranges-error.html
Dim MyNames, i As Long
MyNames = Array("ActualAirtime1", "ActualAllocated1", "ActualData1", "ActualKBAirtime1", _
            "ActualKBAllo1", "ActualMSC1", "ActualText1", "ActualVoice1", "CorrectAirtime1", _
            "CorrectData1", "CorrectKB1", "CorrectMSC1", "CorrectText1", "Credits1", "Debits1")
For i = LBound(MyNames) To UBound(MyNames)
  On Error Resume Next
  ActiveWorkbook.Names(MyNames(i)).Delete
  On Error GoTo 0
Next i
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the DeleteMyRangeNames macro.

 

[e17nel]

I apologize for the delayed response.

To clarify, I am trying to delete named ranges in my spreadsheet using a macro. To start off with, my macro doesn't contain any named ranges. I have a button that creates a summary page and in the process creates named ranges. I also have a button that removes the summary page and deletes the named ranges. Up until recently, the buttons worked fine. Now I get an error when I try and remove the summary page with the button. Hiker95, your "DeleteMyRangeNames" doesn't work because I won't know exactly how many names will be created until the summary button is clicked. However, I tried your "TestV2" and it worked. I am the type of person that likes to know the why behind the what. I have uploaded my spreadsheet to box.net and linked it below. I was wondering if you could take a look at it and see if maybe you could tell me why it's breaking? I'm a self taught VB writer, so
when you see my code, please go easy

https://www.box.com/s/50sc72m9vvu3qj700vrz

Thanks for your help,

Eric

 

[hiker95]

e17nel,

Hiker95, your "DeleteMyRangeNames" doesn't work because I won't know exactly how many names will be created until the summary button is clicked. However, I tried your "TestV2" and it worked. I am the type of person that likes to know the why behind the what. I have uploaded my spreadsheet to box.net and linked it below. I was wondering if you could take a look at it and see if maybe you could tell me why it's breaking? I'm a self taught VB writer, so
when you see my code, please go easy

Thanks for the feedback.

Thanks for the workbook. I will look at it after I get back from picking up my new glasses today.

I have been trying to learn how to use Windows 8 on my new laptop - when you do not know a product the learning curve can be huge.