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

Question

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.

VBasic2008 · Accepted Answer · 2025-03-22 19:51:44Z

Populate a List Box with Matching Rows of Data

Sub forListBoxShow()
    
    ' Reference the range.
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    'Set rg = rg.EntireRow.Columns("A:F") ' if necessary
    
    ' Return the range values in the source Variant array.
    Dim sData() As Variant: sData = rg.Value
    Dim sRowsCount As Long: sRowsCount = UBound(sData, 1)
    Dim ColumnsCount As Long: ColumnsCount = UBound(sData, 2)
    
    ' Define a collection to hold the matching row numbers.
    Dim collRows As Collection: Set collRows = New Collection
    collRows.Add 1 ' headers
    
    ' Loop through the values of the first column and for each non-blank
    ' value, add the row number to the collection.
    Dim sRow As Long
    For sRow = 2 To sRowsCount ' skip headers
        If Len(CStr(sData(sRow, 1))) > 0 Then collRows.Add sRow
    Next sRow
    
    ' Define the destination String array.
    Dim dRowsCount As Long: dRowsCount = collRows.Count
    Dim dData() As String: ReDim dData(1 To dRowsCount, 1 To ColumnsCount)
    
    ' Loop through the elements (source rows) of the collection
    ' and copy the values of the matching rows to the destination array.
    ' Account for errors.
    Dim dRow As Long, Col As Long
    For dRow = 1 To dRowsCount
        sRow = collRows(dRow)
        For Col = 1 To ColumnsCount
            If IsError(sData(sRow, Col)) Then
                dData(dRow, Col) = rg.Cells(sRow, Col).Text
            Else
                dData(dRow, Col) = sData(sRow, Col)
            End If
        Next Col
    Next dRow
    
    ' Populate the list box.
    With Me.listBoxShow
        .ColumnCount = ColumnsCount
        .ColumnWidths = "50,50,70,40,90,90"
        .List = dData
    End With

End Sub

taller · Accepted Answer · 2025-03-22 19:00:18Z

1

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

answered Mar 22 at 19:00

taller

19.7k2 gold badges9 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Populate a List Box with Matching Rows of Data

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Populate a List Box with Matching Rows of Data

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related