Delete Named Range in a specific Worksheet in the current Workbook

[Knylund]

This is a snippet of code in a larger macro, but this one is giving me a 1004 error and I don't know why:

Public testSheet As String
Public nm As Name
Sub DeleteNamedRangeInWorksheet()

For Each nm In ThisWorkbook.Names
If nm.RefersToRange.Worksheet.Name = testSheet Then
nm.Delete
End If
Next nm
End Sub
My goal is resize the Named Range after adding rows to the range. I have a define Named Range routine that works, so I thought I would delete the named range and then readd it. I did try some resizing code and that gave me even more difficulty, since I have multiple worksheets in the current workbook. I am looking for a reusable sub.

 

[My Aswer Is This]

This is a snippet of code in a larger macro, but this one is giving me a 1004 error and I don't know why:

Public testSheet As String
Public nm As Name
Sub DeleteNamedRangeInWorksheet()

For Each nm In ThisWorkbook.Names
If nm.RefersToRange.Worksheet.Name = testSheet Then
nm.Delete
End If
Next nm
End Sub
My goal is resize the Named Range after adding rows to the range. I have a define Named Range routine that works, so I thought I would delete the named range and then readd it. I did try some resizing code and that gave me even more difficulty, since I have multiple worksheets in the current workbook. I am looking for a reusable sub.

Have you considered using Excel Tables?

A Table named Range expands as you enter more values in the column or row.

 

[Tetra201]

See if the following code works for you:

Sub AAA()
    For Each nm In ThisWorkbook.Names
        If Replace(Split(nm.Name, "!")(0), "'", "") = testSheet Then nm.Delete
    Next nm
End Sub

 

[Knylund]

Have you considered using Excel Tables?

A Table named Range expands as you enter more values in the column or row.

thanks will do.

 

[My Aswer Is This]

thanks will do.

Glad I was able to help. Let me know if this works out for you