An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Course Of The Month
4 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
suppressing Excel invalid reference error message
Posted on 2012-12-27
hey guys,
previously mbizup helped me out with deriving this code. but after i run it and it successfully deletes all the named ranges, it still pops up with the invalid reference error message. i'm really not sure how to suppress this error message. could yall help me? thanks!!
previously mbizup helped me out with deriving this code. but after i run it and it successfully deletes all the named ranges, it still pops up with the invalid reference error message. i'm really not sure how to suppress this error message. could yall help me? thanks!!
On Error Resume Next
Application.DisplayAlerts = False
Dim nr As Name
For Each nr In ActiveWorkbook.Names
Debug.Print nr.Name
nr.Delete
Next
Exit Sub
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
Active 7 days ago
Hello,
The code deletes all named ranges in the workbook.
The error message states that a formula uses a reference that does not exist. This reference may well be a range name that has just been deleted. The formula can be in a cell, in data validation or in a conditional formatting. You'll need to find the formula and then delete THAT.
Can you step back a bit and explain what you are trying to achieve overall? Why do you want to delete all range names?
cheers, teylyn
The code deletes all named ranges in the workbook.
The error message states that a formula uses a reference that does not exist. This reference may well be a range name that has just been deleted. The formula can be in a cell, in data validation or in a conditional formatting. You'll need to find the formula and then delete THAT.
Can you step back a bit and explain what you are trying to achieve overall? Why do you want to delete all range names?
cheers, teylyn
Active 1 day ago
At the start of the code put
Application.DisplayAlerts = False
And at the end put
Application.DisplayAlerts = True
Application.DisplayAlerts = False
And at the end put
Application.DisplayAlerts = True
hi there, i have a macro that deletes all the sheets in the excel workbook and then imports those sheets again and then creates the named ranges again. this is done everytime the user runs the report. so when the sheets are deleted, some weird named ranges are created and thus i want to "clean up" before i create the new named ranges again.
the formulas are in the graphs and i need a way to suppress the error messages so it doensn't interupt my code, suppress excel from breaking the code cause of the void in the named ranged references, and then when my later code runs it will relink up the named ranges.
btw application.displayalerts doesn't work but thanks for the help!!
the formulas are in the graphs and i need a way to suppress the error messages so it doensn't interupt my code, suppress excel from breaking the code cause of the void in the named ranged references, and then when my later code runs it will relink up the named ranges.
btw application.displayalerts doesn't work but thanks for the help!!
Active 7 days ago
As far as I'm aware, there is no easy way to suppress the message. The topic has recently been discussed among Excel MVPs, with no satisfactory outcome.
One approach that seems to work is to clear the ranges instead of deleting them, but if you are deleting whole sheets, this may not be an option.
The problem has been around for a long time, apparently, and mainly strikes with charts.
Sorry I cannot give a more helpful reply other than: You're not alone.
cheers, teylyn
One approach that seems to work is to clear the ranges instead of deleting them, but if you are deleting whole sheets, this may not be an option.
The problem has been around for a long time, apparently, and mainly strikes with charts.
Sorry I cannot give a more helpful reply other than: You're not alone.
cheers, teylyn
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month4 days, 20 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.