Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Delete a name on a sheet

I have a name that is screwing up a macro.  Sometimes it exists and other times it does not.

How would I tell Excel, to only delete this if it exists on the sheet, if it does not exist continue. -R-
ActiveSheet.Names("Format").Delete

Open in new window

0
RWayneH
Asked:
RWayneH
3 Solutions
 
Haris DulicCommented:
this will clear all names from the excel
Sub NoNames()
  For Each n In ActiveWorkbook.Names
    n.Delete
  Next n
End Sub

Open in new window

0
 
StephenJRCommented:
To just delete that name, perhaps this:
On Error Resume Next
ActiveWorkbook.Names("Format").Delete
on error goto 0

Open in new window

0
 
jan24Commented:
StephenJR is right: there is no way to test for existence of an object in a collection, so you just have to use On Error Resume Next.
By the way, you probably already know this, but bear in mind that in Excel there are names which exist in the whole workbook - ActiveWorkbook.Names, and there are names which only exist in a sheet ActiveWorksheet.Names.  You might want to delete the name from both collections just to be on the safe side.  Modifying StephenJR's code to achieve this:
On Error Resume Next
ActiveWorkbook.Names("Format").Delete
ActiveSheet.Names("Format").Delete
on error goto 0 

Open in new window

0
 
RWayneHAuthor Commented:
Thanks for the help. -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now