+ Reply to Thread
Results 1 to 10 of 10

Can't delete named ranges using VBA

  1. 01-27-2019, 10:18 AM #1
    frankt68
    frankt68 is offline
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Can't delete named ranges using VBA

    Hi!
    With the help of the VBA code, I created several named ranges. The name consists of the word "Range" and the line number in which the range begins (eg "Range1", "Range44", "Range205", without ").
    I can delete all of these ranges manually in the Names Manager.
    However, I have trouble deleting these ranges using the VBA code belove
    Please Login or Register  to view this content.
    It gives me Run-time error 1004: The syntax of this name isn't correct.
    d65c0210-cd74-4feb-87a0-6f91f03f8282.png

    I would appreciate any advise on how to resolve this. I'm using excel 365.

    Frank
    Reply With QuoteRegister To Reply
  2. 01-27-2019, 10:23 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Can't delete named ranges using VBA

    Works for me.


    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 01-27-2019, 10:26 AM #3
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Can't delete named ranges using VBA

    How about this.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  4. 01-27-2019, 05:22 PM #4
    Winon
    Winon is offline
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Can't delete named ranges using VBA

    @ bakerman2,

    Your formula does not work! I have pasted it in the formula bar and it does not delete anything. It just shows the text!
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Reply With QuoteRegister To Reply
  5. 01-27-2019, 08:09 PM #5
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Can't delete named ranges using VBA

    I have pasted it in the formula bar
    You infidel, are you on something right now ?

    Tjaart, I guess you posted in the wrong thread because to my knowledge this is still VBA-code.
    Reply With QuoteRegister To Reply
  6. 01-28-2019, 02:43 AM #6
    frankt68
    frankt68 is offline
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: Can't delete named ranges using VBA

    Thanx TMS and bakerman2.


    bakerman2 unfortunately, your code does not work for me.

    However, after quite some time spent on searching and trying I found a solution that works.

    Please Login or Register  to view this content.
    The code is not mine, but I can't remember where I found it.
    Reply With QuoteRegister To Reply
  7. 01-28-2019, 03:57 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: Can't delete named ranges using VBA

    Thanks for letting us know. The only thing that's different is the error trapping. I'd still be interested in seeing a sample workbook to try and analyse what it fails on. I had even tried adding a Table and that was happily ignored.
    Reply With QuoteRegister To Reply
  8. 01-28-2019, 05:01 AM #8
    frankt68
    frankt68 is offline
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Talking Re: Can't delete named ranges using VBA

    Hi TMS!

    I attached the files. "Original.xlsx" is the original file, "Original after adding ranges.xlsx" is a file with added ranges using a VBA code and some modification (eg summing quantities in column J) and "Original after removing ranges.xlsx" is a file with all named rages removed, using previously mentioned code.

    I'm sorry about the language in these files.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-28-2019, 05:30 AM #9
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,912

    Re: Can't delete named ranges using VBA

    The issue is the hidden names created for backwards compatibility of new functions. You can test for that explicitly like this:

    Please Login or Register  to view this content.
    Rory
    Reply With QuoteRegister To Reply
  10. 05-02-2021, 05:38 AM #10
    rakeshkhedkar
    rakeshkhedkar is offline
    Registered User
    Join Date
    04-30-2021
    Location
    Pune
    MS-Off Ver
    10
    Posts
    1

    Re: Can't delete named ranges using VBA

    This code worked perfectly for me.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to delete strange named ranges?
    By jaryszek in forum Excel General
    Replies: 17
    Last Post: 12-20-2018, 04:52 AM
  2. [SOLVED] Out of Memory when trying to delete named ranges
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-12-2018, 10:21 AM
  3. [SOLVED] Macro to delete 2 named ranges
    By bevc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-30-2016, 02:37 AM
  4. [SOLVED] Delete all Named Ranges in another workbook
    By tejay in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-15-2015, 03:43 AM
  5. Duplicate Named Ranges, how to specify which one to delete
    By treygriffin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2013, 10:45 AM
  6. Delete named ranges from selection
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2012, 05:20 PM
  7. delete a bunch of named ranges
    By Takeadoe in forum Excel General
    Replies: 2
    Last Post: 06-30-2006, 07:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1