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