New to arrays-how do I build new array while filtering first array?

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

I think this is closer but I am still missing something.

(Extract only this time)

'   build new array
    Dim varNewArray() As Variant
    ReDim varNewArray(1)
    varNewArray(1) = ""
    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 new array

            ReDim Preserve varNewArray(UBound(varNewArray) + 1)
            varNewArray(UBound(varNewArray)) = varMainArray(i, 1), varMainArray(i, 3), varMainArray(i, 4)
       End If
    Next i

stumped. Runtime error 9. Below is the latest version... (If you add a exit sub line just before the code building the new array, the error message doesn't appear. So it's definitely the sub array that's causing the issue)

Private Sub EmployeeList_Populate()
    int_employer_id = 22 'for testing
    sWbSheet = "employee10"

    ' 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
    Dim intMaxRow As Integer
    Dim intMaxCol As Integer
    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(2, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A2
        End With
        .Close ' close the source workbook without saving changes
        Set wbSource = Nothing
    End With

'   build new array
    Dim varNewArray() As Variant
    ReDim varNewArray(1, 3)
    varNewArray(1, 3) = ""
    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 new array
            ReDim Preserve varNewArray(UBound(varNewArray) + 1)
            varNewArray(UBound(varNewArray), 1) = varMainArray(i, 1).Value
            varNewArray(UBound(varNewArray), 2) = varMainArray(i, 3).Value
            varNewArray(UBound(varNewArray), 3) = varMainArray(i, 4).Value
        End If
    Next i

'   populate listbox
    Me.ListBox1.List = varNewArray

    Application.ScreenUpdating = True
End Sub

if you have a 2D array you can only resize the last dimension if you use redim preserve

Originally Posted by JosephP

if you have a 2D array you can only resize the last dimension if you use redim preserve

I am new to arrays. However I am already using redim preserve in the code in post #3.

So where am I going wrong?

I had a friend have a go at the code and here is his version. However I still get a runtime error 9 subscript out of range message.

I have stepped through the code and it all runs smoothly, checking through the main array etc. UNTIL it reaches a match. As soon as VBA goes from this line:

ReDim Preserve varNewArray(2, UBound(varNewArray, 2) + 1)

to this line

varNewArray(0, UBound(varNewArray)) = varMainArray(i, 1)

it gets the error message. Why?

Option Explicit

Private wbSource As Workbook
Private sWbSheet As String
Private varMainArray As Variant

Private Sub UserForm_Initialize()
    Call EmployeeList_Populate
End Sub

Private Sub CancelButton_Click()
    Unload Me
End Sub

Private Sub OKButton_Click()
    If ListBox1.ListIndex = -1 Then
        MsgBox "No employee selected!"
        Unload Me
        Exit Sub
    End If

    MsgBox ListBox1.Value 'for test purposes

    Unload Me
End Sub
'
'-----form macros above, private macros below--------------------------------
'
Private Sub testme()
    Me.Show
End Sub

Private Sub EmployeeList_Populate()
    int_employer_id = 3 'for testing
    sWbSheet = "employee10"

    ' 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
    Dim intMaxRow As Integer
    Dim intMaxCol As Integer
    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(2, 1), Worksheets(sWbSheet).Cells(intMaxRow, intMaxCol)) 'used range starting from A2 (to avoid header)
        End With
        .Close ' close the source workbook without saving changes
        Set wbSource = Nothing
    End With

'   build new array
    Dim varNewArray() As Variant
    ReDim varNewArray(2, 0)

    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 new array
            ReDim Preserve varNewArray(2, UBound(varNewArray, 2) + 1)
            varNewArray(0, UBound(varNewArray)) = varMainArray(i, 1)
            varNewArray(1, UBound(varNewArray)) = varMainArray(i, 3)
            varNewArray(2, UBound(varNewArray)) = varMainArray(i, 4)
        End If
    Next i

'   populate listbox
    Me.ListBox1.List = varNewArray

    Application.ScreenUpdating = True
End Sub

Solved.

Thank you all for the help

for others' benefit it would be good if you posted the answer :-)

I didn't review the code but suspect you needed varNewArray(0, UBound(varNewArray, 2)) = varMainArray(i, 1)