# Variable number of rows in an array

#### Nogslaw

##### Well-known Member
I have the following code that loads an array of data into a listbox on a userform.

Code:
``````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:

Code:
``````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?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Jonmo1

##### MrExcel MVP
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

##### MrExcel MVP
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.
Code:
``````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

##### Well-known Member
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!

Last edited:

Replies
11
Views
725
Replies
7
Views
4K
Replies
1
Views
481
Replies
3
Views
408
Replies
3
Views
680

Threads
1,191,718
Messages
5,988,263
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back