Delete all named ranges from worksheet

[Martin_H]

Hi,

would it be possible to transform this macro to delete named ranges only from active worksheet?

Thank you.

Sub DeleteNames()
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
xName.DELETE
Next
End Sub

 

[veyselemre]

Hi,
try this.

Sub deleteNames()
    Dim xName As Name
    For Each xName In Application.ActiveWorkbook.Names
        If Split(xName.RefersTo, "!")(0) = "=" & ActiveSheet.Name Then
            xName.Delete
        End If
    Next
End Sub

 

[sykes]

See Mike Rickson's post #6 here.

 

[veyselemre]

See Mike Rickson's post #6 here.

according to the method in the link;

Sub deleteNames()
    Dim xName As Name
    For Each xName In Application.ActiveWorkbook.Names
        If xName.RefersToRange.Parent.Name = ActiveSheet.Name Then
            xName.Delete
        End If
    Next
End Sub

 

[Martin_H]

Awesome.

Thank you guys.

 

[sykes]

Pleasure!

 

[Martin_H]

according to the method in the link;

Sub deleteNames()
    Dim xName As Name
    For Each xName In Application.ActiveWorkbook.Names
        If xName.RefersToRange.Parent.Name = ActiveSheet.Name Then
            xName.Delete
        End If
    Next
End Sub

Not sure why, but I am getting a 1004 error.

Application-defined or object defined error with this line lighlighted:

If xName.RefersToRange.Parent.Name = ActiveSheet.Name Then

 

[veyselemre]

If the name pointing to more than one field is defined as in the picture, it will give an error. In this case, the code in message 2 will check the page name of the first field and will work. If there is a name definition pointing to an area on two separate pages, it will look at the first name in the name definition.

 

[sykes]

Martin

This should work; NB test on a COPY of your work, until proven. NB - place the code in the "ThisWorkbook" module, in your project.

Sub deleteNames()
Dim xName As Name
    
With Me
    For Each xName In .Names
        If InStr(xName, "!") > 0 Then
           If xName.RefersToRange.Parent.Name = .ActiveSheet.Name Then
                xName.Delete
           End If
        End If
    Next
End With
End Sub

 

[Martin_H]

Martin

This should work; NB test on a COPY of your work, until proven. NB - place the code in the "ThisWorkbook" module, in your project.

Sub deleteNames()
Dim xName As Name
   
With Me
    For Each xName In .Names
        If InStr(xName, "!") > 0 Then
           If xName.RefersToRange.Parent.Name = .ActiveSheet.Name Then
                xName.Delete
           End If
        End If
    Next
End With
End Sub

Hi Sykes, I've tried it, but there seems to be a problem with the keyword Me.

Invalid use of Me keyword.