Trying to check if defined name exists and then delete it but I get run time error (using answer previously posted to this forum)

[bdautrich]

DomInicB posted

Good morning kenreavy

This little routine will return a msgbox if the named range exists and will do nothing if it doesn't. Any good?

Sub Test()
On Error GoTo Last
If Len(ThisWorkbook.Names("MyRange").Name) <> 0 Then MsgBox "Range Exists"
Last:
End Sub

HTH

As an answer to the question posted a while back.



I thought this answer would apply to my situation. I am trying to check is a defined name exists and then delete it if it does. The spreadsheet has a defined name of Strains. Here is the code below .

If Len(ThisWorkbook.Names("Strains").Name) <> 0 Then Names("Strains").Delete

I get a run time error 1004 Application defined or object defined error. I tried changing ThisWorkbook to ActiveWorkbook but got the same error.

 

[Joe4]

Try this:

If Len(ThisWorkbook.Names("Strains").Name) <> 0 Then Range("Strains").Delete

 

[bdautrich]

Joe4

Thanks for the response that did not work.

I separated it out and the ActiveWorkbook.Names(“Strains”).Name causes the error

Bruce

 

[MARK858]

That error normally means the Named range doesn't exist, are you sure that "Strains" is the exact correct name including no leading or trailing spaces?

 

[bdautrich]

No I checked with the Name Managers and it exists and is spelled exactly the same way.

That error normally means the Named range doesn't exist, are you sure that "Strains" is the exact correct name including no leading or trailing spaces

 

[MARK858]

And definitely no leading or trailing spaces? If you are not sure run the code below (Change Sheet2 to a blank sheets codename if there is already a sheet with the Codename Sheet2 that isn't blank).

Column C of the line with Strains should say 7

Sub ShowNames()
    Dim Sht As Worksheet, nm As Name

    Set Sht = Sheet2 'Make you change the sheet reference if needed

    On Error Resume Next

    For Each nm In Names
        With Sht.Range("A" & Rows.Count).End(xlUp)(2)
            .Value = nm.Name
            .Offset(, 1) = "'" & nm.RefersTo
            .Offset(, 2).Formula = "=Len(" & Sht.Range("A" & Rows.Count).End(xlUp).Address & ")"
        End With
    Next nm
  
    On Error GoTo 0

    Sht.Range("A:C").Columns.AutoFit
End Sub

 

[bdautrich]

And definitely no leading or trailing spaces? If you are not sure run the code below (Change Sheet2 to a blank sheets codename if there is already a sheet with the Codename Sheet2 that isn't blank).

Column C of the line with Strains should say 7

Sub ShowNames()
    Dim Sht As Worksheet, nm As Name

    Set Sht = Sheet2 'Make you change the sheet reference if needed

    On Error Resume Next

    For Each nm In Names
        With Sht.Range("A" & Rows.Count).End(xlUp)(2)
            .Value = nm.Name
            .Offset(, 1) = "'" & nm.RefersTo
            .Offset(, 2).Formula = "=Len(" & Sht.Range("A" & Rows.Count).End(xlUp).Address & ")"
        End With
    Next nm
 
    On Error GoTo 0

    Sht.Range("A:C").Columns.AutoFit
End Sub

I copied the code into my VBA code and ran it.

Here is the output for the Strain row. I think it shows that it exists.

Strains!Strains

=Strains!$A$1:$A$151

15​

 

[MARK858]

Here is the output for the Strain row. I think it shows that it exists.

Column A should just say Strains not Strains!Strains.
Is your name at the workbook or sheet level?

 

[MARK858]

If it is at the worksheet level (which it looks like it is, and the worksheet is called Strains) then you need to use

If Len(ThisWorkbook.Names("Strains!Strains").Name) <> 0 Then Names("Strains!Strains").Delete

 

[bdautrich]

That worked! Thanks so much!!!

If it is at the worksheet level (which it looks like it is, and the worksheet is called Strains) then you need to use

If Len(ThisWorkbook.Names("Strains!Strains").Name) <> 0 Then Names("Strains!Strains").Delete