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.

 

[MARK858]

You're welcome

 

[bdautrich]

Well declared victory too early. I re-entered the code send by Mark858. The Strains sheets is there and the defined names of Strains is on that sheet from the output of the code Mark858 provided. Here is the output.

Strains

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

7​

Here is the code that is generating the run time error. The error is still run time error 1004. Application defined or object defined error.

' Delete defined names so we do not get duplicates

MsgBox ("ThisWorkbook.Names(Strains!Strains).Name = " & ThisWorkbook.Names("Strains!Strains").Name)
MsgBox ("Len(ThisWorkbook.Names(Strains!Strains).Name) = " & Len(ThisWorkbook.Names("Strains!Strains").Name))

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

What the application is doing is copying worksheets from a previous version of the application I am maintaining. These worksheets have defined names on them so if I just copy the sheets I get duplicates and I want to used the defined names from the sheets I copied.

Thanks for any help you can provide

Bruce

 

[bdautrich]

I

Well declared victory too early. I re-entered the code send by Mark858. The Strains sheets is there and the defined names of Strains is on that sheet from the output of the code Mark858 provided. Here is the output.

Strains

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

7​

Here is the code that is generating the run time error. The error is still run time error 1004. Application defined or object defined error.

' Delete defined names so we do not get duplicates

MsgBox ("ThisWorkbook.Names(Strains!Strains).Name = " & ThisWorkbook.Names("Strains!Strains").Name)
MsgBox ("Len(ThisWorkbook.Names(Strains!Strains).Name) = " & Len(ThisWorkbook.Names("Strains!Strains").Name))

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

What the application is doing is copying worksheets from a previous version of the application I am maintaining. These worksheets have defined names on them so if I just copy the sheets I get duplicates and I want to used the defined names from the sheets I copied.

Thanks for any help you can provide

Bruce

I found away around problem. Took out check to see if defined name exists and it works