Please see code below. I am struggling with building the new array for the listbox on the form.
(Unless it would be faster/more efficient just to filter & delete values in the original array?? But I don't know how/if this is even possible)
![]()
Private Sub EmployeeList_Populate() sWbSheet = "employee10" Dim intMaxRow As Integer Dim intMaxCol As Integer Dim varMainArray As Variant ' turn screen updating off (prevent the user from seeing the source workbook being opened) ' open the source workbook as ReadOnly Application.ScreenUpdating = False Set wbSource = Workbooks.Open(sServerFolderProgram & sServerFileDatabaseExcel, False, True) ' create array from worksheet range With wbSource With .Worksheets(sWbSheet) .Activate intMaxRow = .Range("A1").End(xlDown).Row 'there are no blanks in row 1 data intMaxCol = .Range("A1").End(xlToRight).Column 'there are no blanks in column A data varMainArray = .Range(Worksheets(sWbSheet).Cells(1, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A1 End With .Close False ' close the source workbook without saving changes Set wbSource = Nothing End With ' build new array Dim varNewArray As Variant Dim i As Integer For i = LBound(varMainArray) To UBound(varMainArray) If varMainArray(i, 2) = int_employer_id Then Help NEEDED! array is expected to be 5 columns wide by variable rows deep I need to check if column 2 (B) value is = int_employer id. If yes then add values from column 1 (A), column 3 & 4 (C & D) to a new array (3 columns by X rows deep) 'varNewArray = varMainArray(i, 1) End If Next i ' populate listbox Me.ListBox1.List = varNewArray Application.ScreenUpdating = True End Sub











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks