Solved

suppressing Excel invalid reference error message

Posted on 2012-12-27
5
432 Views
Last Modified: 2013-01-17
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!!

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

Open in new window

invalid-reference-pop-up-error-m.png
0
Comment
Question by:developingprogrammer
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 38726257
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38727604
At the start of the code put

Application.DisplayAlerts = False

And at the end put

Application.DisplayAlerts = True
0
 

Author Comment

by:developingprogrammer
ID: 38777135
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!!
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 38777402
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
0
 

Author Closing Comment

by:developingprogrammer
ID: 38791958
ok no problem, but thanks for helping me confirm this teylyn!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now