Unable to delete name range

[ryan8200]

I have tried to delete name range in Sheet2 using method 1 and method 2 as below but not workable. One of the name range contains underscore. Error message for both method:
Method 1: Run time error 1004. The syntax of the name is incorrect
Method 2: Run time error 1004. Application-defined or object-defined error

Method 1

Sub DeleteNamedRanges()

Dim MyName As Name
For Each MyName In Names

    ActiveWorkbook.Names(MyName.Name).Delete

Next

Method 2

Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String

' Put in name of sheet where the range is located
Sht = "Sheet2"

For Each n In ActiveWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n

End Sub

 

[HaHoBe]

Hi ryan8200,

maybe try

Sub DeleteNames()

  Dim nmeName     As Name

  If ActiveWorkbook.Names.Count = 0 Then
    MsgBox "No names found", vbInformation, "Message"
    Exit Sub
  End If

  For Each nmeName In ActiveWorkbook.Names
    nmeName.Delete
  Next nmeName

End Sub

Ciao,
Holger

 

[ryan8200]

Hi ryan8200,

maybe try

Sub DeleteNames()

  Dim nmeName     As Name

  If ActiveWorkbook.Names.Count = 0 Then
    MsgBox "No names found", vbInformation, "Message"
    Exit Sub
  End If

  For Each nmeName In ActiveWorkbook.Names
    nmeName.Delete
  Next nmeName

End Sub

Ciao,
Holger

Hi HaHoBe, thanks for input. Can the name range refer to sheets that consist underscore (Eg: Sales_2022)?

 

[HaHoBe]

Hi ryan8200,

it worked on my test. But the comparision seems to be case sensitive so I slightly altered the code to look like

Sub Delete_My_Named_Ranges_mod()
Dim n As Name
Dim strShName As String

' Put in name of sheet where the range is located
strShName = "Test_Underscore"

For Each n In ActiveWorkbook.Names
  If UCase(n.RefersToRange.Worksheet.Name) = UCase(strShName) Then
    n.Delete
  End If
Next n

End Sub

Ciao,
Holger