Deleting Specified Named Ranges

[HangMan]

I have around 50 named ranges in a Workbook and I need to delete a number of them. I've tried a couple of approaches but I'm getting an error with each one.

The Error I'm getting is '1004' - Application-defined or object-defined error

Please Login or Register  to view this content.

Anyone know why I'm getting the error?

I also tried this but ran into an error!

Please Login or Register  to view this content.

Many thanks

[MickG]

Have you tried :- Range(nr).Delete

[HangMan]

I haven't, but will. I'm curious to know why the two existing approaches give me an error though?

Will report back later...

[LeoTaxi]

this walks tru all names and ask to delete

Please Login or Register  to view this content.

Kind regards
Leo

[Trebor76]

Anyone know why I'm getting the error?

The code is trying to delete a named range that doesn't exist. Make sure the names in your array are all valid named range or turn off error messages.

HTH

Robert

[HangMan]

Hi Trebor76,

I'm not sure I follow. I started by printing out a list of all the named ranges in the workbook using this piece of code

Please Login or Register  to view this content.

I then copied and pasted the names in to various possible solutions but I get an error every single time.

If I simply type:

Please Login or Register  to view this content.

It works fine which shows the name exists, but I really don't want to have to type that line 50 times hence trying to find a better solution.

This is what I've tried so far, all solutions from different websites, but each one gives me an error:

Please Login or Register  to view this content.

- This gives me a '1004' - Application-defined or object-defined error

Please Login or Register  to view this content.

Solution 2 - This gives me a '1004' - Method 'range' of 'object_global' failed error

Please Login or Register  to view this content.

Solution 3 - This gives be a '1004' - The name you entered is not valid, reasons can incude... error

Please Login or Register  to view this content.

My code is sitting within:

Please Login or Register  to view this content.

but still no joy, so I'm completely bafled.

[HangMan]

Hi Leo,

Your code works, but it also gives a '1004' Application-defined or 'object-defined' error at the end. Isn't sandwiching the code between

Please Login or Register  to view this content.

just disguising the error?

What I need though is a solution where I can define which named ranges to keep or alternatively which to delete without the user saying 'Yes' or 'No' to each. This needs to happen effectively in the background without the user being aware there are named ranges being deleted at all.

One thing that baffled me was that it also listed three 'named ranges' which I haven't created and are not visible in the 'Names' dialogue, namely:

xlfm.COUNTIFS
xlfm.ERROR
xlfm.SUMIFS

Are these built in defaults or something? Would there be implcations if they were deleted as well?

Many thanks

[HangMan]

Hi MickG,

I tried Range(nr).Delete but I still get the same error I'm afraid.

Many thanks

[LeoTaxi]

are one, two, three ....... the real names ?

[HangMan]

Hi Leo,

No, but the real names take the format Name, Name_Something, Name_Something_Else. The sheet is sensitive which is why I didn't put the real names.

[LeoTaxi]

Hi Hangman,

in item you put all the names like exemple below

Please Login or Register  to view this content.

Kind regards
Leo

[HangMan]

Hi Leo,

A couple of things, firstly the code runs and deletes 'most' of the named ranges, but I still get the '1004' - Application-defined or 'object-defined' error after the code has run which is a problem because I can't have the user seeing this error.

I have a list of around 50 or so named ranges I want to delete, how can I enter these with line breaks in the code?

If I do the usual "One, Two, Three, Four _
Five, Six, Seven"

etc., it won't accept it, the text is then highlighted in red

There are a couple of named ranges which relate to pivot table slicers, they have no value and refer to nothing, so it probably doesn't matter if they don't get deleted, but for whatever reason, even though they are listed, they don't automatically get deleted using the code.

Please Login or Register  to view this content.

[LeoTaxi]

Hi Hangman,

i dont have experience with tables or pivot tables but i think
you cant remove those names.

so the, on error resume next will handle the job for the other names

Please Login or Register  to view this content.

Kind regards
Leo

[LeoTaxi]

and just see you use the , after the name dont do only the name a space and next name


Kind regards
Leo

[HangMan]

Leo,

That seems to have done the trick, simple when you know how...

Many thanks for your help, it is very much appreciated...