+ Reply to Thread
Results 1 to 7 of 7

Getting Subscript out of range error with For Loop when deleting worksheets

  1. #1
    Registered User
    Join Date
    02-17-2008
    Posts
    21

    Getting Subscript out of range error with For Loop when deleting worksheets

    Ok, I thought this would be simple but it has me stumped.

    I have a workbook with many (up to a hundred or more) worksheets generated by a large macro.

    I have two in the front and two and the end I want to keep - the rest I want to delete.
    Thought it would be a simple loop, but it keeps giving me a Subscript out of range error around loop 9. The actual worksheet order in VBA varies from Sheet1 to Sheet 30 or more, and the order is scrambled.

    Please Login or Register  to view this content.
    Anyone see anything obvious?
    Thanks.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Getting Subscript out of range error with For Loop when deleting worksheets

    Hi parodytx,

    Try something like this:
    Please Login or Register  to view this content.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Getting Subscript out of range error with For Loop when deleting worksheets

    This will delete all sheets except the ones referred in the code:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Getting Subscript out of range error with For Loop when deleting worksheets

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    02-17-2008
    Posts
    21

    Re: Getting Subscript out of range error with For Loop when deleting worksheets

    So is it that after each sheet is deleted, the total worksheets.count automatically updates (decreases by 1)? So that when the WS_count is say 7, but the loop is at 8, the error hits?

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Getting Subscript out of range error with For Loop when deleting worksheets

    Yes,

    Worksheets.Count tells how many worksheets are in the workbook. It will update each time you delete another sheet. WS_count isn't Worksheets.Count. You need to use the naming conventions that the Excel Object Model uses and needs.

  7. #7
    Registered User
    Join Date
    02-17-2008
    Posts
    21

    Re: Getting Subscript out of range error with For Loop when deleting worksheets

    Thanks. Always appreciate learning something useful.

+ 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. Loop variable subscript out of range
    By maurice1991 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2015, 07:31 AM
  2. Accessing worksheets: error 9: subscript out of range
    By sandysoo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2014, 10:52 AM
  3. [SOLVED] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  4. Subscript out of Range Error with using Worksheets.Select on an existing Worksheet
    By SDaniela in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-21-2014, 05:23 AM
  5. Runtime Error - Subscript out of Range / Object Error
    By JHRice in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2012, 05:14 PM
  6. Defining Array - Runtime error 9, Subscript out of range error
    By MaartenW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2012, 07:32 AM
  7. Type Mismatch error & subscript out of range error
    By Jeff Wright in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2005, 03:06 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