Error Fix [Runtime error 9]

[anrichards22]

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]

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]

If you remove that unneeded code what happens?

[cgabe001]

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]

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.