Remove hidden Range Names by VBA [Doubt]

[vijay.vizzu]

Dear All


Please find below code, i found this code in search engine. In this code why confirmation through msg box required to delete the hidden names & if i removed msgbox line then it gives error. Is there any to delete all hidden names without any confirmation required

[pre]

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
MsgBox (xName)
xName.Delete
End If
Next xName
End Sub

[/pre]
Regards

Vijay

 

[jeffreyweir]

Works fine for me. Note that the above code deletes names whether they are hidden or not. Although it only displays a messagebox if they are hidden.

 

[vijay.vizzu]

Dear jeffreyweir


Have tried after delete the statement MsgBox (xName). If you will delete this line, then it will gives an error message. Please try

 

[jeffreyweir]

I have tried. It works fine on my system. Code below. What error are you getting and what line is highlighted?

[pre]

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.Delete
End If
Next xName
End Sub

[/pre]

 

[jeffreyweir]

Also note that this code deletes all names, visible or otherwise.


I'm off to bed now, will check this thread tomorrow.

 

[vijay.vizzu]

Dear jeffreyweir


I have removed the msgbox line, and it gives error and highlighted the after else statement i.e. xname.delete.


Ok i will try again , revert to you


Thanks a lot


Regards

Vijay