Dynamic Array

Robb

Board Regular
Joined
Feb 17, 2002
Messages
145
Goodday,
I am trying to load rows into a listbox that meet certain criteria (so not every row will be loaded). What I have right now works but only prints the last row, so is there a way to Redim the Upperbound without erasing the contents of the array?

Private Sub UserForm_Activate()
Dim intRow As Integer
Dim intCol As Integer
Dim End_Flag As Boolean
Dim intCounter As Integer
Dim intRowCount As Integer
Dim i As Integer

' Row and Column for the Export Data
intRow = 3
intCol = 219

Sheets("DATA").Select
Cells(intRow, intCol).Select

lstExport.ColumnCount = 6

End_Flag = False
While (End_Flag = False)
If ActiveCell.Value = True Then
'intCounter = intCounter + 1
' Load it onto lstExport
intRow = ActiveCell.Row
Redim MyArray(intCounter,6)
MyArray(intCounter, 0) = ActiveSheet.Range("HS" & intRow).Value
MyArray(intCounter, 1) = ActiveSheet.Range("HT" & intRow).Value
MyArray(intCounter, 2) = ActiveSheet.Range("HU" & intRow).Value
MyArray(intCounter, 3) = ActiveSheet.Range("HV" & intRow).Value
MyArray(intCounter, 4) = ActiveSheet.Range("HW" & intRow).Value
MyArray(intCounter, 5) = ActiveSheet.Range("HX" & intRow).Value
intCounter = intCounter + 1
' Move down 1 row
ActiveCell.Offset(1, 0).Select
Else
' We have reached the end of the file
If Len(ActiveCell.Value) = 0 Then
End_Flag = True
Else
' Move down 1 row
ActiveCell.Offset(1, 0).Select
End If
End If
Wend

' Show the Listbox with the values
lstExport.List = MyArray

End Sub

_________________
Thanx.
This message was edited by Robb on 2002-05-09 11:39
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I found a way around my problem, see code below (if anyone else can do it better, please let me know, thanx).

Option Explicit
Dim MyArray(300, 6) As Variant 'Array containing column values for ListBox.

Private Sub UserForm_Activate()
' Load all Styles that have their Export to Booking Report checked off
' Sort it by Style number in ascending order
Dim intRow As Integer
Dim intCol As Integer
Dim End_Flag As Boolean
Dim intCounter As Integer
Dim intRowCount As Integer
Dim i As Integer
Dim UpHer As Integer
Dim MySecArray As Variant

' Row and Column for the Export Data
intRow = 3
intCol = 219

UnHide_WS "DATA"
Application.ScreenUpdating = False
Sheets("DATA").Select
Cells(intRow, intCol).Select

' If the first one is blank then exit
If Len(ActiveCell.Value) = 0 Then
MsgBox "No Data Found."
Hide_WS "DATA"
Unload Me
Exit Sub
End If

lstExport.ColumnCount = 6

End_Flag = False
While (End_Flag = False)
If ActiveCell.Value = True Then
' Load it into an array
intRow = ActiveCell.Row
MyArray(intCounter, 0) = ActiveSheet.Range("HS" & intRow).Value
MyArray(intCounter, 1) = ActiveSheet.Range("HT" & intRow).Value
MyArray(intCounter, 2) = ActiveSheet.Range("HU" & intRow).Value
MyArray(intCounter, 3) = ActiveSheet.Range("HV" & intRow).Value
MyArray(intCounter, 4) = ActiveSheet.Range("HW" & intRow).Value
MyArray(intCounter, 5) = ActiveSheet.Range("HX" & intRow).Value
intCounter = intCounter + 1

' Move down 1 row
ActiveCell.Offset(1, 0).Select
Else
' We have reached the end of the file
If Len(ActiveCell.Value) = 0 Then
End_Flag = True
Else
' Move down 1 row
ActiveCell.Offset(1, 0).Select
End If
End If
Wend

intCounter = intCounter - 1

ReDim MySecArray(intCounter, 6)

For i = 0 To intCounter
MySecArray(i, 0) = MyArray(i, 0)
MySecArray(i, 1) = MyArray(i, 1)
MySecArray(i, 2) = MyArray(i, 2)
MySecArray(i, 3) = MyArray(i, 3)
MySecArray(i, 4) = MyArray(i, 4)
MySecArray(i, 5) = MyArray(i, 5)
Next i

'Populate the listbox with values
lstExport.List = MySecArray

End Sub
 
Upvote 0
Hi Mark,

"Preserve" didn't work (I did try it prior to posting). Preserve does not allow change to the UBound, it only allows change to LBound. Kinda silly eh? Unless....I'm missing something.
 
Upvote 0
Yeah, you're missing something you initially declared your array like this:

<pre>
Dim MyArray(300, 6) </pre>

This isn't a dynamic array.
Try something like this:

<pre>
Dim MyArray()

ReDim MyArray(300, 6)</pre>

Then trying "Preserve" - ing later on.
 
Upvote 0
It looks like Robb wants to resize the first element in his array and Preserve will allow him to only resize the last element. Robb, could you switch things around. Probably be a lot of work...
 
Upvote 0
I tried switching around (and it did work, again I did this before my first posting) but it is unmanageable and ugly. What was Bill thinking?

Actually it wasn't that much work to switch it around.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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

Which adblocker are you using?

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
Back
Top