PDA

View Full Version : Delete Hidden Name Ranges



bleeg10
10-09-2007, 01:49 PM
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
10-09-2007, 02:26 PM
The first named range in your list is invalid in some way. If you delete it manually then your code runs ok.

bleeg10
10-09-2007, 03:02 PM
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
10-09-2007, 03:22 PM
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
10-09-2007, 05:53 PM
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).:banghead:

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:dunno :banghead: , but only if you use :=RefersToR1C1 but not :=RefersTo :banghead: :banghead:


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

unmarkedhelicopter
10-10-2007, 03:51 AM
Ah ! ... more consistent coding from the guys at Microsoft ...