excelvba

How to display items in listbox with column 1 of Excel not equal to empty cell


I have a dynamic sheet here that I would only like to display to listbox, columns A to F with column A/Name not equal to null/empty cell.

A B C D E F
Name Score Complexity Points Total with Points Total without Points
Tom 5 3 0.25 105.00% 100.00%
Brenda 5 4 0.5 110.00% 100.00%
Mark 5 - #VALUE! #VALUE!
- #VALUE! #VALUE!
- #VALUE! #VALUE!

I tried this below during initialize:

Sub forListBoxShow()
    Dim ws As Worksheet, colList As Collection
    Dim arrData, arrList, i As Long, j As Long
    Set colList = New Collection
    Set ws = Worksheets("Sheet1")
    arrData = ws.Range("A1:F" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
    ' build collection of row numbers
    For i = 2 To UBound(arrData)
            If arrData(i, 1) <> vbNullString Then
                colList.Add i, CStr(i)
            End If
    Next
    ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
    For j = 1 To 6
        arrList(1, j) = arrData(1, j) ' header
        For i = 1 To colList.count
                arrList(i + 1, j) = arrData(colList(i), j)
        Next
    Next
    listBoxShow.Clear
    With Me.listBoxShow
        .ColumnCount = UBound(arrData, 2)
        .ColumnWidths = "50,50,70,40,90,90"
        .list = arrList
    End With
End Sub

But I am getting subscript out of range error. I also tried to add real values to cells that have #VALUE! for Name, "Mark", but I have the same error. (For cells with #VALUE! - it has default formula and has purpose). Also tried this for code above but I have the same error:

If Not arrData(i, 1) = "" Then

I appreciate your help.


Solution

  • The error subscript out of range usually occurs when the array index exceeds the declared size of the array. UBound(arrData, 2) should be used in ReDim as the maximum value of the second dimension.

    ReDim arrList(1 To colList.Count + 1, 1 To UBound(arrData, 2))