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" <GoFigure.1zhn0y_1133653502.4268@excelforum-nospam.com> wrote in
message news:GoFigure.1zhn0y_1133653502.4268@excelforum-nospam.com...
>
> 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
>

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

What additional logic would it take to do this?