Dynamic Array not Resizing

I have the following code:

Code:

---

Sub DynamicArray()
    Dim numbers() As Integer, size As Integer, i As Integer
    size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
    ReDim numbers(size)
    For i = 1 To size
        numbers(i) = Cells(i, 1).Value
    Next i
      MsgBox numbers(size)
End Sub

---

My worksheet looks like this and when I run my code, I keep getting "10" when the last value in my array is "20". Any ideas? ThanksAttachment 26932

This contains an unqulified reference (in red):
numbers(i) = Cells(i, 1).Value
If the active sheet is not the same sheet as Worksheets(1) then you're likely to get incorrect results (this assumes that the code is sitting in a standard code module and not a sheet's code module - if it's in a sheet's code module then an unqulaified reference refers to that sheet's cells, so make sure the code is in an apppropriate module)
So in the first instance, you could try changing just that line to:
numbers(i) = Worksheets(1).Cells(i, 1).Value

With so many possibilities where it may go wrong, much better to attach a sample file.

Well, FWIW ... I get 8 as an answer, but P45cal could have the issue

Quote:

---

I keep getting "10" when the last value in my array is "20". Any ideas? Thanks

---

I don't see any 20 in the data


Attachment 26934

Code:

---

Option Explicit


Sub DynamicArray()
    Dim numbers() As Integer, size As Integer, i As Integer
   
    size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
   
    MsgBox size
   
    ReDim numbers(size)
   
    MsgBox LBound(numbers) & " -- " & UBound(numbers)
   
    For i = 1 To size
        numbers(i) = Cells(i, 1).Value
    Next i
     
    MsgBox numbers(size)
End Sub

---

P45Cal, your suggestion was spot on! I had my array on sheet2. But as you said, my code only worked when Sheet2 was the active sheet. By qualifying the reference to

Code:

---

For i = 1 To size
        numbers(i) = Worksheets(2).Cells(i, 1).Value
    Next i

---

The code ran just fine on the other sheets. Also thanks to Paul as I learned some new methods to use with arrays. :)