DELETING NAMED RANGES

Emm

Board Regular
Joined
Nov 29, 2004
Messages
165
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Emm

Board Regular
Joined
Nov 29, 2004
Messages
165
.. OR a Sub that can delete all Names using a wildcard would also do it..

Thanks,

Keith
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top