Runtime Error 9: Subscript Out of Range [Fix]

Repair your workbook for any unforeseen errors

How to » Microsoft Office » Microsoft Excel

Reading time icon 4 min. read

Calendar icon Updated on

by Claire Moraa 

updated on

Share this article

Readers help support Windows Report. We may get a commission if you buy through our links. Tooltip Icon

Read our disclosure page to find out how can you help Windows Report sustain the editorial team Read more

Key notes

  • The runtime error 9: Subscript out of range is common in Microsoft Excel, and it occurs when you try to access a cell beyond your worksheet's scope.
  • Since it mostly happens when copying data between workbooks, always check for stray characters or symbols in your formula, as these can cause runtime errors.

If you use a VBA code in your workbook and get a runtime error 9: Subscript out of range, you are trying to reference an object not present in the current context in your Microsoft Excel workbook.

You’ll most likely run into this error when copying data between workbooks, so this is a reason to pay close attention to ensure you’re copying the right thing. Nonetheless, since the error has already occurred, here’s a quick-fix guide.

What is subscript error 9 in Excel?

When you receive the runtime error 9, you are trying to access a cell or range of cells through a subscript that is outside the range of cells defined in your formula.

In other words, you’re trying to get to a place in the worksheet that doesn’t exist. This can happen because of one or more of the following reasons:

  • Outdated Excel app – You may be using an old version of Excel that does not support certain features.
  • Invalid data type in cell – This can result from using invalid characters in a string or from entering an incorrect number. For instance, you can enter the letter O instead of the numerical value 0.
  • Corrupt workbook – If the workbook has been opened in Excel, then closed abruptly, it may end up corrupt hence causing the subscript error 9.
  • Incorrect syntax in the formula – This means that the formula cannot be evaluated correctly because it has been entered incorrectly or there is an error in the syntax of the formula.
  • Using cell references that do not exist – This error can occur if you are referencing cells that are outside the scope of the worksheet or if you are referencing an invalid cell address. It can also happen when copying code from one workbook into another.
  • Incorrectly typed formulas – One of the most common causes for this error is incorrectly typed formulas. For example, if you forget to include a closing parenthesis or bracket, Excel will return this error.

How do I fix Runtime error 9 subscript out of range?

Try the following basic checks before any advanced troubleshooting:

  • Check for any Windows updates and run if available.
  • Ensure your Excel app is up-to-date.
  • Clean up your workbook by checking for any errors in the formulas.
  • Make sure that all cells are referenced correctly in your formula.

1. Save the file in the Excel macro-enabled format

  1. On the Excel file, navigate to File.file
  2. Select Options.
  3. Click on Save, and under Save workbooks, select Excel Macro-Enabled Workbook (*.xlsm).

Macros are programs that are designed to automate tasks in Microsoft Office applications. When you run a macro, it will execute the actions that have been programmed into it.

However, the runtime error 9 may occur in Excel if you fail to save your file in the required format. XLSM files are used for storing macros and charts created using VBA in Excel.

This file type contains macros, which are programming instructions available to the user when they open the file and are required to work properly.

2. Enable macros

  1. Launch MS Excel and click on File.file
  2. Select Options.
  3. Click on Trust Center, then select Trust Center Settings.trust center
  4. Under Macro settings, check the Enable all macros box then hit OK.

When you enable macros, you permit to run the macro in its current state. This means the macro can access all the formulas and functions it needs to run properly.

In its disabled state, only the basic functionality is available, and the user must enter data manually hence why copying between workbooks presents this runtime error 9 in Excel.

3. Repair Excel

  1. Launch MS Excel and open a blank workbook.
  2. Click on File.file
  3. Select Open and click on Browse.
  4. Locate the problematic file, select it, and click on the drop-down icon next to Open.
  5. Select Open and Repair.

In case this manual repair method fails to work, don’t fret. We have a list of advanced Excel repair tools that can easily do the job and repair a wide range of corruption issues.

And that’s a wrap for this article, but be sure to check out other related errors, such as the runtime error 7, and how to navigate such issues.

For any additional thoughts on this error, leave your comments down below.

More about the topics: Microsoft Excel, Runtime Errors

Claire Moraa

Claire Moraa Shield

Windows Software Expert

Claire has a knack for solving problems and improving the quality of life for those around her. She's driven by rationality, curiosity, and simplicity, and always eager to learn more about Microsoft's products. With a background in teaching and reviewing, she breaks down complex topics into easily understandable articles, focusing mostly on Windows 11, errors, and software.