How to Fix Runtime Error 9 Subscript Out of Range in Excel?

In Microsoft Excel, run-time errors generally occur when the files are missing. Such runtime errors are the most varied & complex to fix. However, one such error that users are recently experiencing and reporting is “Run-time error ‘9’: Subscript out of range” when running VBA code in MS Excel. Thus, if you are facing the same error message, read this post till the end. Here, you will learn the common reasons behind the occurrence of this error and how to fix runtime error 9 subscript out of range in Excel successfully.

So, let’s get started…

Quick Fixes:

1. Check the Range of the Array
2. Verify the Worksheet Name in the VBA Code
3. Change the Security Settings of Macro
4. Repair the MS Excel File
5. Best Software to Fix Corrupted Excel Document

What Is Runtime Error Subscript Out of Range 9?

The “VBA Error Subscript Out of Range 9” typically occurs when you try to access an array or a collection using an index that does not exist or is outside the valid range.

Here is how the error message looks like:

Though this run-time error commonly occurs when working with VBA (Visual Basic for Applications) code in the Excel.

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 occur 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 Runtime Error 9 Subscript Out of Range in Excel?

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:

Step 1- First, you must click the Design tab under Developer section.

Step 2- Then, double-tap on a Command button.

Step 3- After this, check & modify the Excel worksheet name (for example from the “emp” to “emp2”).

Step 4- At this time, run the code.

Step 5- 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 runtime error 9 subscript out of range 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:

Step 1- Open your MS Excel.

Step 2- Go to the File then click on Options and then click Trust Center.

Step 3- In the Trust Center, you have to choose the Trust Center Settings.

Step 4- 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 Runtime 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 affected 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 in order to repair a file or extract data.
• Finally, click on Repair option in order 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 the 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.

Download       Buy Now

* Free version of the product only previews recoverable data.

Here are the steps to use this tool:

Bottom Line:

So, this is all about how to fix runtime error 9 subscript out of range in Excel.

All you need to do is to follow the solutions mentioned above in this post to deal with this run-time error. 

Besides, it is suggested to handle your Excel document properly & create a valid backup of the data saved within it to avoid data loss situations.

Good Luck!!!