Run-time error ‘9’: Subscript out of range – array

In this post I want to present You the most common error associated with array creation which is Run-time error ‘9’: Subscript out of range.

At the beginning of my arrays experience I was having that same error over and over again.

Let me show You the example array and the main issue I am writing about.

Code

Sub tests()

Dim arr As Variant
Dim lastRow As Long

With ThisWorkbook.Sheets("Sheet1")
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    arr = .Range(.Cells(1, 1), .Cells(lastRow, 1))
End With

Debug.Print arr(1)

End Sub

In this code I am creating array from only 1 column. After that, I want to print out the value of first element. Instead of this I get Run-time error ‘9’: Subscript out of range. Never know what is wrong, why I am getting error with 1 column array?!

Then one day I found out about Watches. In my words, it is an additional window, which can give a live preview of variables. I mean whole structure, for example properties, values or types. To turn it on You need to right click variable, choose Add Watch… and click OK.

My array have 10 elements. We can see from the screenshot, that every element have specified 2 dimensions! My mind have never thought about second dimension in one-column array. To get rid of the Run-time error ‘9’ modify your array code like this:

Debug.Print arr(1, 1)

When I saw this problem mentioned on StackOverflow I realized, that I am not the only one who was struggling with it.

But don’t worry, there is also another solution for this issue!

You can approach to print out array values like in original code, but to do this array must be transposed. After setting array range You need to add:

   arr = Application.Transpose(arr)

Then your array structure looks like this:

Whole code should looks like this:

Sub tests()

Dim arr As Variant
Dim lastRow As Long

With ThisWorkbook.Sheets("Sheet1")
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    arr = .Range(.Cells(1, 1), .Cells(lastRow, 1))
    arr = Application.Transpose(arr)
End With

Debug.Print arr(1)

End Sub

I hope You guys will never have such problems from now. Remember that using this method of setting array it will always have 2 dimensions, even if it is 1 column range.