DELETING NAMED RANGES

[Emm]

Hi All,

I have this Sub to delete named ranges from a workbook....
Can it be altered to only remove Names from a Worksheet?

Sub DeleteNamedRanges()
Dim MyName As Name

For Each MyName In Names
ActiveWorkbook.Names(MyName.Name).Delete
Next

End Sub

Thanks,

Keith

 

[Emm]

.. OR a Sub that can delete all Names using a wildcard would also do it..

Thanks,

Keith

 

[Johnny C]

Hi Keith

This should give you enough to go on... it's a sub I wrote so I could highlight all names with #REFs and all external refs; it uses a 2 column listbox (which is a pain to work with, it requires rebuilding the list for a few bits). I inherit a lot of spreadsheets what are full of defunct names and multiple occurences of the same name

it's form-driven, the form has the 2column listbox, a button to highlight all #Ref errors, a button to highlight all names with external links, a 'Delete' button and a cancel button. Just create a userform and link the buttons to the right bit of code


Private Sub CommandButton1_Click()
' Actually delete selected names

Dim NamedRanges()
Dim DeleteName$
Dim Counter%

For Counter = 0 To RedundantNamesForm.ListBox1.ListCount - 1
If ListBox1.Selected(Counter) Then
DeleteName = ListBox1.List(Counter)
ActiveWorkbook.Names(DeleteName).Delete
End If
Next

Unload RedundantNamesForm

End Sub

Private Sub CommandButton2_Click()
'Cancel button

Unload RedundantNamesForm

End Sub

Private Sub CommandButton3_Click()
'Select names with external references

Dim DeleteName$
Dim NamedRanges()
Dim NameCounter%, Counter%

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
Next

ReDim NamedRanges(1 To NameCounter, 1 To 2)

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
NamedRanges(NameCounter, 1) = n.Name
NamedRanges(NameCounter, 2) = n
Next

For Counter = 1 To NameCounter
DeleteName = NamedRanges(Counter, 2)
If InStr(1, DeleteName, "]", vbTextCompare) Then ListBox1.Selected(Counter - 1) = True
Next

End Sub

Private Sub CommandButton4_Click()
'Select names with #Ref errors in

Dim DeleteName$
Dim NamedRanges()
Dim NameCounter%, Counter%

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
Next

ReDim NamedRanges(1 To NameCounter, 1 To 2)

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
NamedRanges(NameCounter, 1) = n.Name
NamedRanges(NameCounter, 2) = n
Next

For Counter = 1 To NameCounter
DeleteName = NamedRanges(Counter, 2)
If InStr(1, DeleteName, "#REF", vbTextCompare) Then ListBox1.Selected(Counter - 1) = True
Next

End Sub

Private Sub UserForm_Initialize()

With RedundantNamesForm.ListBox1
.RowSource = ""
.ColumnWidths = "150;"
End With

Dim NamedRanges()

Dim NameCounter%, Counter%
NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
Next

ReDim NamedRanges(1 To NameCounter, 1 To 2)

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
NamedRanges(NameCounter, 1) = n.Name
NamedRanges(NameCounter, 2) = n
Next
ListBox1.List = NamedRanges
Counter = 0

End Sub

 

[Johnny C]

Sorry, forgot to add the code that calls the dialog (should be obvious, the form is called RedundantNamesForm. The userform_initialise above populates the list in the userform

Sub DeleteRedundantNamesDialog()

RedundantNamesForm.Show

End Sub

 

[Johnny C]

In fact your specific request seems like a handy function so I added it to my macro, here's the code, just add another button

Private Sub CommandButton5_Click()
'Select names in the current worksheet

Dim DeleteName$
Dim NamedRanges()
Dim NameCounter%, Counter%
Dim CurrentSheetName$

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
Next

ReDim NamedRanges(1 To NameCounter, 1 To 2)

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
NamedRanges(NameCounter, 1) = n.Name
NamedRanges(NameCounter, 2) = n
Next

CurrentSheetName = ActiveSheet.Name

For Counter = 1 To NameCounter
DeleteName = NamedRanges(Counter, 2)
If InStr(1, DeleteName, CurrentSheetName, vbTextCompare) Then ListBox1.Selected(Counter - 1) = True
Next

End Sub