Delete named ranges beginning with a string
 

How do I delete all ranges named "startX" where X is the index number
for a worksheet in the active workbook?

This is a non-elegant solution to the following problem. Surely someone
has an elegant solution ...

I am using the following code to create an index (table of contents) of
worksheets in the active workbook. Another procedure creates a new
worksheet for a new customer by copying the "New Customer" worksheet
with the new customer's name as the worksheet name. When this
create-a-new-customer procedure runs, this ugly-but-understandable
error occurs:

A formula or sheet you want to move or copy contains the name
'wrn.Q1.', which already exists on the destination worksheet. Do you
want to use this version of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formula or worksheet, click
No, and enter a new name in the Name Conflict dialog box.

Since the person who commissioned this "simple" project is a pure
novice, I'm trying to circumvent unnecessarily confusing situations.
Since the informational dialog box should always be clicked Yes, I want
to either find a way not to display it or remove the cause.

In any case here's code (as somewhat modified from a Web page).
Apologies in advance for the lack of formatting:

Private Sub Worksheet_Activate()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim wSheetIndex As Long
Dim M As Long
M = 1
With Me
..Columns(1).ClearContents
..Cells(1, 1) = "Customer Index"
..Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
ActiveSheet.Calculate
If wSheet.Name < Me.Name Then

' Don't want an index entry for menu sheet
If wSheet.Name < "MenuSheet" Then
' Don't want an index entry for New Customer template
sheet
If wSheet.Name < "New Customer" Then
M = M + 2
With wSheet
..Range("A1").Name = "Start" & wSheet.Index
..Hyperlinks.Add Anchor:=.Range("B1:C1"),
Address:="", _
SubAddress:="Index", TextToDisplay:="Return
to Index"
With .Cells.Range("B1:C1")
..Merge
..Interior.ColorIndex = 6
..Interior.Pattern = xlSolid
..HorizontalAlignment = xlCenter
..VerticalAlignment = xlCenter
..Font.Bold = True
End With
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1),
Address:="", _
SubAddress:="Start" & wSheet.Index, _
TextToDisplay:=wSheet.Name
End If
End If
End If
Next wSheet
ActiveSheet.Range("A1").Select
End Sub

Any help and suggestions would be greatly appreciated.

- Al (aka "Go Figure")


--
GoFigure
------------------------------------------------------------------------
GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274
View this thread: http://www.excelforum.com/showthread...hreadid=490463

Delete named ranges beginning with a string
 

see if this will do it for you


Sub name_ranges() 'this deletes all named ranges
Dim nm As Name

Select Case MsgBox("Are you Sure You Want To Delete All Named Ranges " & _
Chr(10) & "that begin with Start?", _
vbOKCancel Or vbExclamation Or vbDefaultButton1, Application.Name)
Case vbOK
For Each nm In ThisWorkbook.Names
If UCase(Left(nm.Name, 5)) = "START" Then
nm.Delete
End If
Next nm
Case vbCancel
Exit Sub
End Select

End Sub
--


Gary


"GoFigure" wrote in
message ...

How do I delete all ranges named "startX" where X is the index number
for a worksheet in the active workbook?

This is a non-elegant solution to the following problem. Surely someone
has an elegant solution ...

I am using the following code to create an index (table of contents) of
worksheets in the active workbook. Another procedure creates a new
worksheet for a new customer by copying the "New Customer" worksheet
with the new customer's name as the worksheet name. When this
create-a-new-customer procedure runs, this ugly-but-understandable
error occurs:

A formula or sheet you want to move or copy contains the name
'wrn.Q1.', which already exists on the destination worksheet. Do you
want to use this version of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formula or worksheet, click
No, and enter a new name in the Name Conflict dialog box.

Since the person who commissioned this "simple" project is a pure
novice, I'm trying to circumvent unnecessarily confusing situations.
Since the informational dialog box should always be clicked Yes, I want
to either find a way not to display it or remove the cause.

In any case here's code (as somewhat modified from a Web page).
Apologies in advance for the lack of formatting:

Private Sub Worksheet_Activate()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim wSheetIndex As Long
Dim M As Long
M = 1
With Me
Columns(1).ClearContents
Cells(1, 1) = "Customer Index"
Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
ActiveSheet.Calculate
If wSheet.Name < Me.Name Then

' Don't want an index entry for menu sheet
If wSheet.Name < "MenuSheet" Then
' Don't want an index entry for New Customer template
sheet
If wSheet.Name < "New Customer" Then
M = M + 2
With wSheet
Range("A1").Name = "Start" & wSheet.Index
Hyperlinks.Add Anchor:=.Range("B1:C1"),
Address:="", _
SubAddress:="Index", TextToDisplay:="Return
to Index"
With .Cells.Range("B1:C1")
Merge
Interior.ColorIndex = 6
Interior.Pattern = xlSolid
HorizontalAlignment = xlCenter
VerticalAlignment = xlCenter
Font.Bold = True
End With
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1),
Address:="", _
SubAddress:="Start" & wSheet.Index, _
TextToDisplay:=wSheet.Name
End If
End If
End If
Next wSheet
ActiveSheet.Range("A1").Select
End Sub

Any help and suggestions would be greatly appreciated.

- Al (aka "Go Figure")


--
GoFigure
------------------------------------------------------------------------
GoFigure's Profile:
http://www.excelforum.com/member.php...fo&userid=4274
View this thread: http://www.excelforum.com/showthread...hreadid=490463

Delete named ranges beginning with a string
 

Gary, thanks, that would work if I wanted to delete all names that begi
with the string "start". But I only want to delete the start-string
that have a single-digit or double-digit suffix and no charaters afte
the numerical suffix. So, the code needs to delete only names of th
forms startN and startNN, where N and NN are are numerical digit
between 1 and 99.

What additional logic would it take to do this

--
GoFigur
-----------------------------------------------------------------------
GoFigure's Profile: http://www.excelforum.com/member.php...nfo&userid=427
View this thread: http://www.excelforum.com/showthread.php?threadid=49046