Hi, im using this code now trying to populate my listbox without success. Brief background on what this code does:
when users make a selection in combobox2, it checks column D of worksheet "Lookup" and finds rows that match the selection. The corresponding value in column B is transferred as header to a worksheet in different columns that matches the combobox selection. It then pulls data from sheets that matches the header and populates in the rows below the header. Now after all these are populated, im trying to transfer these data into a listbox. Column number may exceed 10, that's why i can't use .Additem function. The number of rows and columns to populate in the listbox may differ based on combobox2 selection, that's why i need both rows and columns to be dynamic. Can anyone please help me with this?

sorry for all the unused variables listed as i was trying alot of different methods prior to posting

Private Sub ComboBox2_Change()
    Dim ws, ws1, ws2 As Worksheet
    Dim lRow, lRow2, lRow3, lCol, x, y, a, b, c, d, e As Long
    Dim rng As Range
    Dim i, j As Integer
    Dim myarray As Variant
    
    With Me
    .ComboBox1.ListIndex = -1 = ComboBox2.ListIndex <> -1
    End With
    
    If ComboBox2.Value = "" Then
    Exit Sub
    End If

    
    Me.ListBox1.Clear
    
    With Me.ListBox1
    If ComboBox2.Value <> "" Then
    Set ws = Worksheets("Lookup")
    Set ws1 = Worksheets(ComboBox2.Value)
    ws1.Range("D:Z").Delete Shift:=xlToLeft
    lRow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(0, 0).Row
    lRow2 = ws1.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(0, 0).Row
    lCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
    For x = 2 To lRow
        If ws.Cells(x, 4) = ComboBox2.Value Then
            ws1.Cells(1, lCol) = ws.Cells(x, 2)
            lCol = lCol + 1
        End If
    Next x
    For Each ws2 In Sheets
    For y = 4 To lCol
    If ws1.Cells(1, y) = ws2.Name Then
    j = 2
    For i = 2 To 50
    If ws2.Cells(i, 1).Value <> "" Then
      ws1.Cells(j, y).Value = ws2.Cells(i, 6).Value
    j = j + 1
    End If
    Next i
    End If
    Next y
    Next
    Set rng = (ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row & ws2.Range("A" & ws2.Columns.Count).End(xlToLeft).Column)
    myarray = rng
    .List = myarray
    End If
    End With
    
   



End Sub