Delete Hidden Name Ranges

[bleeg10]

Hi guys,
How do I delete hidden name ranges? The following code below unhides the hidden name ranges, but it won't delete them! I have to manually delete them one by one, and it's annoying. If you run the macro on the attached file, you get an error message. Please help me!

Sub Remove_Hidden_Names()
       ' Dimension variables.
       Dim xName As Variant
       ' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names
           If xName.Visible = True Then
               xName.Delete
            Else
                xName.Visible = True
                xName.Delete
            End If
Next xName
End Sub

[lucas]

The first named range in your list is invalid in some way. If you delete it manually then your code runs ok.

[bleeg10]

Lucas,
Do you know what's invalid about the name? I know it's a really long name, do you think that has something to do with it?

[lucas]

I honestly don't know. It seems like just a letter and numbers with no spaces.....should b ok but there is something wrong there for sure.

[Paul_Hossler]

My guess is that D01203 (or DO1203) looks like a cell address to Excel and it won't let you delete it from VBA, but you can from the worksheet side (go figure).

However, just to make sure that you stay confused, you cannot add that as a name from the worksheet side, but you can from the VBA side , but only if you use :=RefersToR1C1 but not :=RefersTo

[vba]
Sub Remove_Hidden_Names()

Dim i As Long
For i = ActiveWorkbook.Names.Count To 1 Step -1
With ActiveWorkbook.Names(i)
.Visible = True
.Delete
End With
Next i
End Sub

Sub Add_Hidden_Names()

'works
' ActiveWorkbook.Names.Add Name:="D01203", RefersToR1C1:="=R1C1", Visible:=False

' does not work
ActiveWorkbook.Names.Add Name:="D01203", RefersTo:="=A1", Visible:=False

End Sub


[/vba]

[unmarkedhelicopter]

Ah ! ... more consistent coding from the guys at Microsoft ...