+ Reply to Thread
Results 1 to 5 of 5

VBA to Loop through array of named ranges

  1. #1
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    VBA to Loop through array of named ranges

    Hi. I have a list of named ranges that I want to delete the contents of any unlocked cells within each range. All of the ranges are on the same worksheet.

    I know I can loop through each named range individually as in the example below, but I was hoping to not have to copy this 7 times, changing the name of the range each time.

    How can it be done in an array? My named ranges are: RangeN_All with N being 1-7.


    Please Login or Register  to view this content.

  2. #2
    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,093

    Re: VBA to Loop through array of named ranges

    Maybe

    Please Login or Register  to view this content.
    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


  3. #3
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: VBA to Loop through array of named ranges

    Quote Originally Posted by TMS View Post
    Maybe

    Please Login or Register  to view this content.
    Thank you. This worked great except that it only cleared ranges 1-6. I checked the 7th named range and it is named correctly and the range is correct, so I'm not sure why it didn't clear the last range.

  4. #4
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: VBA to Loop through array of named ranges

    Quote Originally Posted by LKERN View Post
    Thank you. This worked great except that it only cleared ranges 1-6. I checked the 7th named range and it is named correctly and the range is correct, so I'm not sure why it didn't clear the last range.
    So sorry. I figured it out. I had neglected to unlock the cells that need to be unlocked. Your code works perfectly!

  5. #5
    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,093

    Re: VBA to Loop through array of named ranges

    You're welcome. Thanks for the rep.

+ 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. [SOLVED] Using Named Ranges for a loop statement
    By Neuk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2018, 01:47 PM
  2. Loop Defining Named Ranges
    By lopiner in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-20-2017, 02:57 PM
  3. Add named ranges via loop
    By aBc894543 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2016, 11:04 AM
  4. [SOLVED] Loop through columns and named ranges
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 06-02-2015, 12:14 PM
  5. Replies: 2
    Last Post: 06-01-2011, 07:43 AM
  6. [SOLVED] named ranges in a For Loop
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-22-2005, 11:10 AM
  7. Looking up named ranges as an array
    By L.White in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2005, 05: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