how to suppress error message excel vba

developingprogrammer
developingprogrammer used Ask the Experts™
on
hey guys, how do i suppress this error message? i already tried application.displayalerts = false and added on error resume next, but when i run the code (F5), once it hits the errors it exits.
error pop up
i'm trying to delete all the named ranges in excel

Sub test()
Dim x As Integer
x = 1
Application.DisplayAlerts = False
On Error Resume Next
For x = 1 To 100
    ActiveWorkbook.Names(x).Delete
Next
Application.DisplayAlerts = True
End Sub

i tried do until err<>0 but the same problem still happens - once it hits an invalid reference it stops. thanks guys!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
It's better to write code that avoids errors by design as much as possible.   Is the error occurring after all worksheets are deleted?

Instead of using 100 as an upper limit, use the number of worksheets.

Application.worksheets.count
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry you're dealing with named ranges.

I'm not an Excel expert, but does this get the total count?

ActiveWorkbook.names.count
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
Just got a chance to test this.  This seems to work without error:

    Dim nr As Name
    
    For Each nr In ActiveWorkbook.Names
        Debug.Print nr.Name
        nr.Delete
    Next

Open in new window


Anyhow, the idea is to write code that avoids errors by design, and to add error handling to trap unexpected errors.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
<No Points wanted>

Basic/Generic error handler:

Sub YourSubName()
On Error GoTo Err_YourSubName

********** Your Code Goes Here **********

Exit_YourSubName:
    Exit Sub

Err_YourSubName:
    MsgBox "There was an error executing the command." _
    & vbCrLf & "Error " & Err.Number & ": " _
    & vbCrLf & Error, vbExclamation
    Resume Exit_YourSubName
End Sub
whao guys, no points wanted and researching an unfamiliar topic just to help me, i'm really touched and appreciative of how yall take the time to help me out in the problems i face. maybe i'm a bit naive but i definitely couldn't imagine someone going out of the way to help their fellows or someone they don't know - i'm really appreciative guys = )

pratima thanks for looking into this but i think docmd is for Access not Excel, i tried it in Excel and it couldn't work - not found in the object library. but thanks! = )
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<whao guys, no points wanted and researching an unfamiliar topic just to help me, i'm really touched and appreciative of how yall take the time to help me out in the problems i face.>

...In my case, I only posted a very basic example of a generic error handler.
In other words, ...any expert here could have posted the same thing...
(No research needed)

My post did nothing to actually "answer" your question, it was just extra info.

In the end, and to be honest, ...I have done well this year "point-wise", so I saw no reason to appear greedy for a simple post like that.

So AFAICT, you can still request that my post be removed from the points split.

..."There will always be another Question"

;-)

Jeff
ha thanks for being generous jeff, and it's not so much that you didn't answer the question but rather you took time to read through my question and add that info there so i can learn. so it's really much more of your time spent contributing that i want to show my appreciation for = ) thank you!! = ))
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Good Luck in the New Year!

;-)

Jeff
thanks Jeff! = )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial