Avatar of developingprogrammer
developingprogrammer
 asked on

suppressing Excel invalid reference error message

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
Microsoft Excel

Avatar of undefined
Last Comment
developingprogrammer

8/22/2022 - Mon
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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
Martin Liss

At the start of the code put

Application.DisplayAlerts = False

And at the end put

Application.DisplayAlerts = True
developingprogrammer

ASKER
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!!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
developingprogrammer

ASKER
ok no problem, but thanks for helping me confirm this teylyn!