Variable number of rows in an array

[Nogslaw]

I have the following code that loads an array of data into a listbox on a userform.

Private Sub UserForm_Initialize()
Dim c As Integer
Dim r As Integer
Dim MyArray(41, 4)

ListBox1.ColumnCount = 4
c = 0
r = 0

'Load values MyArray - 4 columns
LR = Range("A65536").End(xlUp).Row
For Each cell In Range("A2:D" & LR).SpecialCells(xlCellTypeVisible)
        MyArray(r, c) = cell
        c = c + 1
        If c = 4 Then
        c = 0
        r = r + 1
        End If
Next cell
    
'Load ListBox1
ListBox1.List() = MyArray

End Sub

Is there a way to change the dimensions of the array if the list goes beyond 41 rows? I've tried using Redim MyArray(51,4), but I get an error message stating the array has already been dimensioned. Is there a way to do a variable number of rows in the array? I also tried using soemthing like:

For x = range("A65536").end(xlup).row
Dim MyArray(x, 4)

but that gave me an error message too saying I couldn't use variables. Any suggestions?

 

[Jonmo1]

Don't define the dimensions of the array in the Dim line
Just use

Dim MyArray() As Variant

Then the redim statement in each loop will work.

Use a counter that incriments each loop, something like

Counter = Counter + 1

Redim Preserve MyArray(1 to Counter, 4)

Do stuff in the loop

Next Counter

 

[mikerickson]

If you declare an array with a fixed size you can't ReDim it later.
Even so, ReDim will work only on the last index.
Using the transpose of myArray should get you what you want.

Private Sub UserForm_Initialize()
Dim cell As Range
Dim c As Integer, LR As Long
Dim r As Integer
Dim MyArray() As String

ReDim MyArray(1 To 4, 1 To 41)
ListBox1.ColumnCount = 4
c = 1
r = 1

'Load values MyArray - 4 columns
LR = Range("A65536").End(xlUp).Row
For Each cell In Range("A2:D" & LR).SpecialCells(xlCellTypeVisible)
        MyArray([U]c, r[/U]) = cell
        c = c + 1
        If c = 5 Then
        c = 1
        r = r + 1
        End If
Next cell

ReDim Preserve MyArray(1 To 4, 1 To 42)
MyArray(1, 42) = "new1"
MyArray(2, 42) = "new2"
MyArray(3, 42) = "new3"
MyArray(4, 42) = "new4"
'Load ListBox1
ListBox1.List() = Application.Transpose(MyArray)

End Sub

 

[Nogslaw]

Jonmo1 - I used your suggestion and it worked just how I had hoped. Thank you so much!

Mikerickson - I haven't tested your solution yet, but I definitely will. It's always good to know a couple of approaches to solving a problem.

Thank you both for your help!