• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error while doing Redim Preserve


Hi All,
My following code works fine but the array is static. I want to make it dynamic. When I tried doing it, it shows error as subscription out of range. Can you please let me know where I am going wrong?

>>> use code - tags <<<
Option Explicit

Public Sub Button1_Click()

Dim i As Integer
Dim strPath As String
Dim strArr(1 To 999, 1 To 1) As String '  I want to make it dynamic array
Dim strFile As String

'ReDim strArr(1 To 999, 1 To 1)

Cells(8, 3).ClearContents

strPath = Sheet1.Cells(4, 3).Value

If Right(strPath, 1) <> "\" Then
    strPath = strPath & "\"
End If

i = 1
strFile = Dir(strPath & "*")

Do While strFile <> vbNullString

'ReDim Preserve strArr(1 To i, 1 To 1)

strArr(i, 1) = strFile

strFile = Dir()

i = i + 1


Cells(8, 3).Resize(i) = strArr

End Sub
Last edited by a moderator:


Excel Ninja
Please reread Forum Rules
Please post, new posts in the correct forums,
You opened this thread in Ask an Excel Question.
Isn't this
VBA Macros -question.
While You're rereading those,
You'l notice few more steps eg from
How to get the Best Results at Chandoo.org.
In this time, I'm moving this thread.


Well-Known Member
Using Redim with Preserve, you can only alter the upper boundary of the last dimension, not the first one as you are trying. You don't actually need a 2D array until you populate the cells, so use a 1D array prior to that (which you can directly use Redim Preserve with), then use Application.Transpose when populating the cells.