Fix Excel Run Time Error 9 Subscript Out of Range in 5 Simple Ways!

Excel is one of the widely used spreadsheet programs. It is famous for its versatility & efficiency in handling data. However, users often come across various runtime errors while handling the Excel worksheet, and one of the common ones is “Run time error 9 subscript out of range.” This blog aims to shed light on this specific error, its major reasons, and troubleshooting methods to overcome it in no time with less effort.

Here is how the error message looks like:

So, let’s get started…

Quick Fixes:

What Is Error 9 Subscript Out of Range in Excel?

Runtime error 9 subscript out of range in Excel typically occurs when you try to access an array or a collection within the spreadsheet using an index that does not exist or is beyond the valid range.

In simple words, this warning message commonly appears when the index specified in the VBA (Visual Basic for Applications) code is outside the defined range but Excel is unable to find the object referenced in the code.

Common Reasons for Excel VBA Runtime Error 9: Subscript Out of Range

There are a wide variety of reasons that can lead to this run-time error message in Excel. Below I have listed the most common factors that can cause this annoying error:

1. Entered an incorrect statement syntax of an array.
2. The entry or object that you’re trying to use in the VBA code is either deleted or not defined earlier.
3. If variable name spelling is incorrect then you can receive this error.
4. Referenced an inappropriate array element.
5. The Excel workbook is corrupted in which you are trying to use the VBA.
6. An unspecified number of elements in the array.
7. Specified an unacceptable element.

How to Fix Excel Run Time Error 9 Subscript Out of Range?

Now, let’s follow the below troubleshooting fixes to solve run-time error 9 subscript out of range Excel 2013/2016/2019.

Solution 1- Check the Range of the Array

As already mentioned in the causes section, the unspecified number of elements in the array of the code can trigger this VBA runtime error.

For example – Suppose you’ve stated an array & forgot to state the array variable along with the elements, you can see the below-shown error:

However, in order to resolve this, you have to specify an array variable like this:

Also Read: Fix VBA Error 400 Running An Excel Macro?

Solution 2- Verify the Worksheet Name in the VBA Code

Another method that you should try is to check if the worksheet name isn’t defined properly in the VBA code.

For example– When you try to copy the content from one MS Excel document to another Excel document by VBA code, and you have mentioned an incorrect worksheet name then you can get the below error:

Now, when you try to run the code mentioned above, the MS Excel will show the “VBA runtime error 9”.

If you want to check the worksheet name and correct it, follow the below steps:

• First, you must click the Design tab under Developer section.
• Then, double-tap on a Command button.
• After this, check & modify the Excel worksheet name (for example from the “emp” to “emp2”).

• At this time, run the code.
• The data in the ‘emp’ sheet will be copied to the ‘emp2’.

Solution 3- Change the Security Settings of Macro

Most of the time, this VBA error subscript out of range 9 also occurs in Excel if the macros are restricted in Macro Security Settings. However, in that case, you need to check & change macro settings immediately.

Follow these steps:

• Open your MS Excel.
• Go to the File then click on Options and then click Trust Center.
• In the Trust Center, you have to choose the Trust Center Settings.
• Here, click on the Macro Settings, choose the Enable all macros >> click on OK.

If this method won’t work for you, try the next one.

Also Read: Fix Excel Runtime Error 13 Type Mismatch

Solution 4- Repair the MS Excel File to Fix Run Time Error 9 Subscript Out Of Range

Sometimes, the name of the objects might get transformed due to file corruption. When the objects aren’t recognized in the VBA code, you may receive this runtime error.

In such a situation, you can go for an inbuilt Excel utility known as Open and Repair. It will eventually help you to fix your corrupted Excel file.

Here’s how you can use this tool:

• In your Excel, you have to click on the File > Open.
• After this, browse to find the faulty file.
• Under Open dialog-box, choose a corrupted workbook.
• Under Open dropdown, you have to click on the Open and Repair

• Now, you’ll get a prompt enquiring you to repair a file or extract data.
• Finally, click on Repair option to extract data. If the Repair option fails, then you can click on Extract Data. This will recover the data without values & formulas.

Best Software to Fix Corrupted Excel Document

If the “Open & Repair” utility and other manual methods fail to fix Excel VBA runtime error 9: subscript out of range error, it might be possible that your Excel file got corrupted. In such a case, you are suggested to go with the MS Excel Repair Tool.

It is one of the best & most recommended feature-loaded utilities that can fix any type of problem, corruption, or error in Excel files (.xls, .xltm, .xlsx, .xltx, & .xlsm). Apart from that, you can restore all the objects of your corrupt Excel files such as charts, formulas, cell comments, pivot tables, etc.

So, just must download, install & try this software to repair your corrupt Excel file and deal with the Excel error that you are facing.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

Here are the steps to use this tool:

Frequently Asked Questions

Is Runtime Error 9 Specific to Excel?

No, run time error 9 is not only occurs in Excel program while running VBA code, it can also occur in Access and other database programs when executing macro.

How Do I Fix Runtime Error 9 ': Subscript Out of Range in Excel?

You can fix this error by applying the below tricks:

• Check the Range of the Array
• Verify the Worksheet Name in the VBA Code
• Check the References
• Change the Security Settings of Macro
• Repair the MS Excel File

Can Runtime Error Code 9 in Excel Be Prevented?

Yes, by regularly checking object references, organizing workbooks, and maintaining consistency in Excel versions, you can minimize the likelihood of encountering runtime error 9.

Packing Up

Excel run time error 9 subscript out of range can disrupt workflow efficiency & hinder productivity. Nevertheless, by understanding its causes, and using effective troubleshooting methods discussed in this post, you can easily solve this error and enhance the Excel spreadsheet experience.

Good Luck!!!