Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • 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 DjulicCommented:
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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