excel - is there a limit on .list and listcount - Stack Overflow

I have this code for a form field entry and it seem to limit the number of cells being able to return to 10 items, I would like all of the data to load back into form fields

Any help would be great

Private Sub txtSearch_Change()
'''Search Code''''
On Error Resume Next

If Me.txtSearch.Text = "" Then
Me.DisplayInfo.Clear
Exit Sub
End If

Me.DisplayInfo.Clear
Dim r
Dim last_row As Integer
last_row = Sheet1.Range("A10000").End(xlUp).Row
For r = 8 To last_row
    a = Len(Me.txtSearch.Text)
    If UCase(Left(Sheet1.Cells(r, criteria).Value, a)) = UCase(Me.txtSearch.Text) Then
    With Me.DisplayInfo
    
    
  .AddItem Sheet1.Cells(r, "A").Value
     .List(.ListCount - 1, 1) = Sheet1.Cells(r, "B").Value
     .List(.ListCount - 1, 2) = Sheet1.Cells(r, "C").Value
     .List(.ListCount - 1, 3) = Sheet1.Cells(r, "D").Value
     .List(.ListCount - 1, 4) = Sheet1.Cells(r, "E").Value
     .List(.ListCount - 1, 5) = Sheet1.Cells(r, "F").Value
     .List(.ListCount - 1, 6) = Sheet1.Cells(r, "G").Value
     .List(.ListCount - 1, 7) = Sheet1.Cells(r, "H").Value
     .List(.ListCount - 1, 8) = Sheet1.Cells(r, "I").Value
     .List(.ListCount - 1, 9) = Sheet1.Cells(r, "J").Value
    .List(.ListCount - 1, 10) = Sheet1.Cells(r, "K").Value
    .List(.ListCount - 1, 11) = Sheet1.Cells(r, "L").Value
    .List(.ListCount - 1, 12) = Sheet1.Cells(r, "M").Value
    .List(.ListCount - 1, 13) = Sheet1.Cells(r, "N").Value
    .List(.ListCount - 1, 14) = Sheet1.Cells(r, "O").Value
    End With
    End If
    Next r
    
End Sub

I have this code for a form field entry and it seem to limit the number of cells being able to return to 10 items, I would like all of the data to load back into form fields

Any help would be great

Private Sub txtSearch_Change()
'''Search Code''''
On Error Resume Next

If Me.txtSearch.Text = "" Then
Me.DisplayInfo.Clear
Exit Sub
End If

Me.DisplayInfo.Clear
Dim r
Dim last_row As Integer
last_row = Sheet1.Range("A10000").End(xlUp).Row
For r = 8 To last_row
    a = Len(Me.txtSearch.Text)
    If UCase(Left(Sheet1.Cells(r, criteria).Value, a)) = UCase(Me.txtSearch.Text) Then
    With Me.DisplayInfo
    
    
  .AddItem Sheet1.Cells(r, "A").Value
     .List(.ListCount - 1, 1) = Sheet1.Cells(r, "B").Value
     .List(.ListCount - 1, 2) = Sheet1.Cells(r, "C").Value
     .List(.ListCount - 1, 3) = Sheet1.Cells(r, "D").Value
     .List(.ListCount - 1, 4) = Sheet1.Cells(r, "E").Value
     .List(.ListCount - 1, 5) = Sheet1.Cells(r, "F").Value
     .List(.ListCount - 1, 6) = Sheet1.Cells(r, "G").Value
     .List(.ListCount - 1, 7) = Sheet1.Cells(r, "H").Value
     .List(.ListCount - 1, 8) = Sheet1.Cells(r, "I").Value
     .List(.ListCount - 1, 9) = Sheet1.Cells(r, "J").Value
    .List(.ListCount - 1, 10) = Sheet1.Cells(r, "K").Value
    .List(.ListCount - 1, 11) = Sheet1.Cells(r, "L").Value
    .List(.ListCount - 1, 12) = Sheet1.Cells(r, "M").Value
    .List(.ListCount - 1, 13) = Sheet1.Cells(r, "N").Value
    .List(.ListCount - 1, 14) = Sheet1.Cells(r, "O").Value
    End With
    End If
    Next r
    
End Sub

• excel
• vba
• forms

Share Improve this question Follow edited Feb 1 at 22:11 user692942 16.4k88 gold badges8484 silver badges190190 bronze badges asked Feb 1 at 1:37 Umbra2318Umbra2318 1111 bronze badge 3

• What exactly is the value of criteria and how do you acquire it? Also, we cannot see where you're setting the number of columns of the listbox. Please clarify. – VBasic2008 Commented Feb 1 at 2:59
• it is a global variable and is taken from a different function using a dropdown box to select which column to search in. – Umbra2318 Commented Feb 1 at 15:23
• Referring to your (main?) question "is there a limit on list"box rows: a) theoretically limited by memory, b) in fact afaik limited by a display limitation of ~65k, c) practically you won't never ever use such a huge listbox :-) Fyi you might be interested also in post Populate Listbox with multiple columns showing further hints how to populate a listbox @Umbra2318 – T.M. Commented Feb 4 at 10:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Display Filtered Data in a List Box

• Apparently, you can add a maximum number of 10 columns using your way of populating the list box.
• Returning the filtered values in an array and using the List method is more efficient and allows for more columns.

Main

Private Sub txtSearch_Change()

    Const COLUMNS_ADDRESS As String = "A:O" ' has to start with column 'A'
    Const LAST_ROW_COLUMN As String = "A"
    Const CRITERIA_COLUMN As String = "B" ' adjust!
    Const FIRST_ROW As Long = 8
    
    ' Clear the list box.
    Me.DisplayInfo.Clear
    
    ' Read the text box value.
    Dim SearchText As String: SearchText = Me.txtSearch.Text
    If Len(SearchText) = 0 Then Exit Sub
    
    ' Reference the the sheet and range.
    Dim ws As Worksheet: Set ws = Sheet1
    Dim LastRow As Long:
    LastRow = ws.Cells(ws.Rows.Count, LAST_ROW_COLUMN).End(xlUp).Row
    If LastRow < FIRST_ROW Then Exit Sub ' no data on sheet
    Dim rg As Range: Set rg = ws.Rows(FIRST_ROW & ":" & LastRow) _
        .Columns(COLUMNS_ADDRESS)
    
    ' Return the filtered data in a 2D one-based array.
    Dim Data As Variant:
    Data = GetFilteredRange(rg, CRITERIA_COLUMN, SearchText)
    If IsEmpty(Data) Then Exit Sub ' no filtered data

    ' Populate the list box.       
    With Me.DisplayInfo
        .ColumnCount = rg.Columns.Count ' you must set the number of columns
        '.ColumnWidths = "20,20,30,30,20,20,20,30,20,30,20,20,20,30,30" ' adjust
        .List = Data
    End With
    
End Sub

Help

Function GetFilteredRange( _
    ByVal rg As Range, _
    ByVal ColumnIndex As Variant, _
    ByVal FilterString As String) _
As Variant

    If rg Is Nothing Then Exit Function ' no range
    
    Dim crg As Range
    On Error Resume Next
        Set crg = rg.Columns(ColumnIndex)
    On Error GoTo 0
    If crg Is Nothing Then Exit Function ' invalid column
        
    Dim sRowsCount As Long: sRowsCount = rg.Rows.Count
    
    Dim sData() As Variant:
    If sRowsCount = 1 Then
        ReDim sData(1 To 1, 1 To 1): sData(1, 1) = crg.Value
    Else
        sData = crg.Value
    End If
    
    Dim HasNoMatch As Boolean: HasNoMatch = True
    
    Dim coll As Collection, sRow As Long, CellString As String
    
    For sRow = 1 To sRowsCount
        CellString = CStr(sData(sRow, 1))
        If InStr(1, CellString, FilterString, vbTextCompare) = 1 Then ' begins w
            If HasNoMatch Then Set coll = New Collection: HasNoMatch = False
            coll.Add sRow
        End If
    Next sRow
    
    If HasNoMatch Then Exit Function ' no matching rows
    
    Dim dRowsCount As Long: dRowsCount = coll.Count
    Dim ColumnsCount As Long: ColumnsCount = rg.Columns.Count
    If ColumnsCount > 1 Then sData = rg.Value
    Dim dData() As Variant: ReDim dData(1 To dRowsCount, 1 To ColumnsCount)
    
    Dim dRow As Long, Col As Long
    For dRow = 1 To dRowsCount
        sRow = coll(dRow)
        For Col = 1 To ColumnsCount
            dData(dRow, Col) = sData(sRow, Col)
        Next Col
    Next dRow
        
    GetFilteredRange = dData

End Function