Error Fix [Runtime error 9]

**anrichards22** · 11-30-2012, 02:35 AM

I am using the following code to rename worksheets

Please Login or Register  to view this content.

When it gets to the last worksheet I get a runtime error 9 subscript out of range on the line:

Please Login or Register  to view this content.

The code renames every other sheet as required.
Any suggestions on how to fix the error??

**protonLeah** · 11-30-2012, 02:59 AM

The line: arr = Range("b:b").Value
loads the entire column B into the array so that the upper bound is: 65536
Therefore, i will quickly get to an empty cell and crash.

You need to find the last used cell in B such as:

Please Login or Register  to view this content.

**Norie** · 11-30-2012, 05:19 AM

If you remove that unneeded code what happens?

**cgabe001** · 12-05-2012, 12:07 PM

I am having the same run time error 9 issue using the following code and I have it in a range within 10 cells. It stops on the 4th one. Any suggestions or help would be greatly appreciated

Please Login or Register  to view this content.

**Norie** · 12-05-2012, 12:09 PM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.