Procedure to remove all named ranges

[DoctorV]

I have a workbook which the last step is to email a single sheet to a
user, which works fine. I was also able to delete all vba out of the
sheet using a procedure. The one thing I cannot get to work is to
delete all of the named ranges out of the sheet because when you go to
Insert name define they all show up.

How can I programmatically remove all named ranges. Thanks

 

[Don Guillett]

Here is one and some others I keep in my personal.xls

Sub DeleteAllNames() 'ALL sheets
For Each Name In Names
Name.Delete
Next Name
End Sub

Sub HyperlinksOut()
ActiveSheet.Hyperlinks.Delete
End Sub

Sub ShapesCut()
For Each S In ActiveSheet.Shapes
S.Cut
Next
End Sub

Sub killDeadNames() 'Matthew Henson <[email protected]>
'This macro removes all names that are invalid,
' i.e. point to regions that have been deleted.
Dim pointy As String 'pointy is the place the name
points to
Dim nameCount As Integer 'number of names in workbook or
sheet
Dim i As Integer, j As Integer 'counters
'kill workbook-level names
Let nameCount = ActiveWorkbook.Names.Count
If nameCount >= 1 Then
For i = nameCount To 1 Step -1
pointy = Names(i).RefersTo
If InStr(pointy, "#REF") > 0 Then
'The function is 0 when #REF! is not included
Names(i).Delete
End If
Next i
End If
'kill sheet-level names
If ActiveWorkbook.Worksheets.Count = 0 Then Exit Sub

For j = 1 To ActiveWorkbook.Worksheets.Count
Let nameCount = Worksheets(j).Names.Count
If nameCount >= 1 Then
For i = nameCount To 1 Step -1
pointy = Worksheets(j).Names(i).RefersTo
If InStr(pointy, "#REF") > 0 Then
'The function is 0 when #REF! is not included
Worksheets(j).Names(i).Delete
End If
Next i
End If
Next j
End Sub