How to Delete Named Ranges in A specific Sheet

Hey Everyone,

I have named ranges in a particular worksheet. All the named ranges are specific only. Someone helped me concatenate the appropriate strings. Awesome.

However, I want the user of the worksheet to be able to clear the names of the worksheet before they start naming the ranges. Just in case. I found something else and I'm trying to adapt it

specifically this is where I am:

Sub delnames()

    Dim nName As Name

    Dim shtname As String
    shtname = ActiveSheet.Name


For Each nName In ThisWorkbook.Names
        If nName.RefersTo = "=" & shtname & "'!" Then nName.Delete
    Next


End Sub

I think I may need to use something other than RefersTo. Is there some string specific conditional I might want to use?

Hi,

Try this For Each...Next loop instead:

For Each nName In ThisWorkbook.Names
    If nName.RefersToRange.Worksheet.Name = shtname Then
        nName.Delete
    End If
Next nName

Hope this helps

Or another loop that would also work is:

For Each nName In ThisWorkbook.Names
    If InStr(1, nName, shtname) Then
        nName.Delete
    End If
Next nName

Hey Ajryan88,

Both worked great! The InStr is what I was thinking about. I'll use that next time. For this sort of thing. Thanks again!

No problems! Glad I could help

Please don't forget to mark this thread as solved