How to view All the name ranges and deleted unwanted using a macro

[bhatipuru]

I have workbook with multiple tabs on a worksheet. when I click the name manager nothing happens. How can I view all the names in the workbook and delete the selected names. I try using the macro blow but no luck.

Sub DelRefNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If InStr(n.RefersTo, "#REF!") Then
n.Delete
End If
Next n
End Sub

Also, I have links in the same workbook and would like to remove them. I tried breaking them but no luck.

 

[Fishboy]

I have workbook with multiple tabs on a worksheet. when I click the name manager nothing happens. How can I view all the names in the workbook and delete the selected names. I try using the macro blow but no luck.

Sub DelRefNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
If InStr(n.RefersTo, "#REF!") Then
n.Delete
End If
Next n
End Sub

Also, I have links in the same workbook and would like to remove them. I tried breaking them but no luck.

Hi bhatipuru,

The following macro will create a new sheet and then list all named ranges on the new sheet:

Sub ListNames()
Dim wks As Worksheet

Set wks = Worksheets.Add
wks.Range("A1").ListNames

End Sub

Once you have your list of names and have decided which ones are to be deleted then you can modify the following code to remove the unwanted ranges:

Sub DeleteNamedRanges()

ActiveWorkbook.Names("NamedRange1").Delete
ActiveWorkbook.Names("NamedRange2").Delete
ActiveWorkbook.Names("NamedRange3").Delete

End Sub

 

[bhatipuru]

First of all thank you. the first macro works.

If I'm not mistakes, the list of the names are only for one tab. If, so then there are over 4K names. Is there a faster way to pick & Delete.

Also, how would I do both steps for the entire workbook.

 

[Fishboy]

First of all thank you. the first macro works.

If I'm not mistakes, the list of the names are only for one tab. If, so then there are over 4K names. Is there a faster way to pick & Delete.

Also, how would I do both steps for the entire workbook.

Hi bhatipuru,

The first code I shared will create a list of ALL named ranges within your active workbook. It is not limited to a single sheet.

With regards to deleting the ranges, if you had over 4k named ranges and you didn't want them ALL to be deleted then I am afraid you would need to manually go through the list deciding which ones were to be kept and which ones were to be deleted.

If you were happy for ALL named ranges to be deleted then the following standalone bit of code would do that for you. Remember however that this cannot be undone!

Sub DeleteALLNamedRanges()
Dim xName As Name


For Each xName In Application.ActiveWorkbook.Names
    xName.Delete
Next


End Sub

 

[bhatipuru]

Thank you. sorry to ask stupid questions but I guess I need more help. I have deleted one name at a time and I was able to delete the selected.
After deleting the names, I ran the macro below and its showing only the names I want to keep but when I click on name manager nothing opens up. I believe there are more names in there with #REF which are not being displayed even the macro below.
Sub ListNames()
Dim wks As Worksheet

Set wks = Worksheets.Add
wks.Range("A1").ListNames

End Sub.


Also, I have links in the same workbook and would like to remove them. I tried breaking them but no luck

 

[Fishboy]

Thank you. sorry to ask stupid questions but I guess I need more help. I have deleted one name at a time and I was able to delete the selected.
After deleting the names, I ran the macro below and its showing only the names I want to keep but when I click on name manager nothing opens up. I believe there are more names in there with #REF which are not being displayed even the macro below.
Sub ListNames()
Dim wks As Worksheet

Set wks = Worksheets.Add
wks.Range("A1").ListNames

End Sub.


Also, I have links in the same workbook and would like to remove them. I tried breaking them but no luck

Hmm, I am not sure why there would be other named ranges that aren't shown, however for your link issue there is some good advice in a thread here:

http://www.mrexcel.com/forum/excel-...all-links-visual-basic-applications.html[url]

 

[AlphaFrog]

Here is an advanced name manager add-in you may find useful.

JKP Name Manager