+ Reply to Thread
Results 1 to 2 of 2

Macro to delete range names doesn't work??

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    155

    Question Macro to delete range names doesn't work??

    I am a total newbie with VBA, so this may be a stupid/simple question, but please bear with me...
    I set up a spreadsheet with a macro that downloads stock quotes (the bulk of this macro is based on a macro from another spreadsheet that I found online). I discovered that Excel creates a new range name (ExternalData_1, ExternalData_2, etc.) for each block of quote data downloaded (that is, for each ticker symbol). I want to delete these range names each time the macro runs. So I added this to the macro:

    Dim n As Name
    For Each n In ActiveWorkbook.Names
    If Left(n.Name, 13) = "ExternalData_" Then n.Delete
    Next

    I cannot delete the names individually (using a counter to generate the numerical portion of each name) because I cannot necessarily predict how many quotes will be downloaded, and if the macro tries to delete a name that doesn't exist, it generates an error.

    My problem is that this VBA code does not work (it doesn't delete any range names). However, if I create a test spreadsheet, manually create a bunch of named ranges (ExternalData_1, ExternalData_2, etc.), and then run these same commands in a test macro, it works fine! I think my problem is rooted in the fact that my main workbook contains several worksheets, and the ExternalData_xx range names that are created have a scope of only the single worksheet where the stock quotes are being loaded. Whereas in my test file, the range names I manually create have a scope of "Workbook" rather than just one worksheet. I should also note that the macro button (that triggers the stock quote download) is on a different worksheet than the one where the quotes are loaded (and becomes the sole scope of the new range names).

    How should I modify my VBA code to actually delete the single-worksheet-scoped range names (ExternalData_1, ExternalData_2, etc.)? And please give me the actual code because if you tell me to "make the other worksheet the active worksheet" (or something like that), I don't know how to do that in VBA code!! Thanks for helping this newbie!

  2. #2
    Forum Contributor dlow's Avatar
    Join Date
    08-12-2013
    Location
    EveryWhere
    MS-Off Ver
    Excel 2007, 2010
    Posts
    275

    Re: Macro to delete range names doesn't work??

    I find this helpful.

    I think the Gurus are gonna tell you to show us your code or upload a workbook. I just wanted to share the Link with someone who says that they are new. I know I get a lot of value out of it. The links I shared are for Access, but if you go there Excel is also there, so Good-Luck

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Digital signature doesn't work in a workbook with formula's in names.
    By MichaelHd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-11-2014, 11:13 AM
  2. Replies: 2
    Last Post: 11-08-2012, 05:10 PM
  3. Replies: 1
    Last Post: 02-23-2012, 02:12 PM
  4. Macro, autofilter, delete rows outside date range, defined names as criteria
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2011, 04:10 AM
  5. Auto Filter Delete Rows by Criteria Doesn't Work Range To Complicated
    By robertjtucker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2005, 01:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1