Delete hidden range names

[cdfjdk]

I have a macro that applies protection to ranges in seven worksheets. It gives the ranges a title (required parameter) "Range1"..."Range7".

The problem I have is that if I want to turn the protection off and then reapply it by running the macro again, the macro fails because "Range1"..."Range7" already exist (I get a run-time error "Application-defined or object-defined error").

I can avoid the problem by changing the titles in the macro, e.g. "RangeA"..."RangeG", but this just adds 7 redundant names and next time I want to run the macro again - same problem.

What I therefore want to do is delete the range titles before I re-run the macro. The titles are hidden (not displayed in the Name Manager) and I have tried various combinations of the code below to make them visible without success.

I don't want to delete ALL names, but to be able to select the names I want to delete.

As an alternative, maybe it is possible in the macro to force the range titles to be visible when it creates the ranges? I know it's possible to remove them in the XML, but that seems overkill.

Sub ShowNames()
Dim xName As Name

For Each xName In Application.ActiveWorkbook.Names
    xName.Visible = True
Next xName

End Sub

Any help gratefully received!

 

[xenou]

Hi,
You can delete a hidden name the same way delete a non-hidden name, as far as I know.

A simple solution is to make sure the name is deleted before you assign it (or alternatively you can keep it if it exists and only create it if it doesn't exist).

So, first example (just deleting the name):

Sub foo()

    On Error Resume Next
    ThisWorkbook.Names("_MyName").Delete
    On Error GoTo 0
    
    Range("A1:A10").Name = "_MyName"

End Sub

And second example (create the named range if it doesn't exist already):

Sub Bar()
    
    If Not NameExists("_MyName") Then
        '//Create named range
    End If
    
    '//Do more stuff

End Sub

Private Function NameExists(ByVal strName As String) As Boolean
Dim s As String

On Error Resume Next

    s = ThisWorkbook.Names(strName).Name
    If s <> "" Then
        NameExists = True
    Else
        NameExists = False
    End If

End Function

(untested code - buyer beware).

 

[cdfjdk]

Thank you, Xenou!

I confess I should have checked the XML first to save asking the wrong question! The range title is not saved as a name, but as part of a "protectedrange".

This means that you do not delete the name, but the protected range itself. Code to do this is:

Sub Unprotect_sheets_remove_ProtectedRange()
'Unprotect sheets and delete existing protected ranges

Dim aer As AllowEditRange
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

        Sheets("MySheet1").Select
        ActiveSheet.Unprotect Password:="abc123"
            For Each aer In ActiveSheet.Protection.AllowEditRanges
            aer.Delete
            Next

        Sheets("MySheet2").Select
        ActiveSheet.Unprotect Password:="abc123"
            For Each aer In ActiveSheet.Protection.AllowEditRanges
            aer.Delete
            Next

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Many thanks for your help!