Deleting array with named ranges

[PShingadia]

Hi All:

Been struggling with this all day and would be grateful for any help. I'm trying to use VBA to delete an array of named ranges but keep getting a Run time error '424' object required when I run the following. It's not the complete code but just the part that should undertake the deletions.

Sub DeleteNames

Dim all_names, n As Name

all_names = Array("INFO_YESNO", "LIST_Cost_Types", "LIST_Cost_Types_Excl_Sal", "LIST_Evaluation_Volumes", "LIST_Locations", "LIST_LOTS", "LIST_MarkUp", "LIST_Milestones")


'Delete range names
For Each n In all_names
Names .Delete
Next

End Sub


It stops at line For Each n In all_names

 

[rlv01]

What about something like this instead.

Sub DeleteNames()
    Dim n As name
    Dim WS As Worksheet

    'Delete range names
    'Workbook level names
    For Each n In Names
        Select Case n.name
        Case "INFO_YESNO", "LIST_Cost_Types", "LIST_Cost_Types_Excl_Sal", "LIST_Evaluation_Volumes", "LIST_Locations", "LIST_LOTS", "LIST_MarkUp", "LIST_Milestones"
            n.Delete
        End Select
    Next n

    'Delete range names
    'Worksheet level names
    For Each WS In Worksheets
        For Each n In WS.Names
            Select Case Split(n.name, "!")(1)
            Case "INFO_YESNO", "LIST_Cost_Types", "LIST_Cost_Types_Excl_Sal", "LIST_Evaluation_Volumes", "LIST_Locations", "LIST_LOTS", "LIST_MarkUp", "LIST_Milestones"
                n.Delete
            End Select
        Next n
    Next WS
End Sub

 

[PShingadia]

Wow...thanks very much!!! Worked first time. Really appreciate the help.