CODEWITHSUNDEEP
excelvbafilterlistboxpopulate
fpuglap
fpuglap

Reputation: 119

How to populate userform listbox with a filtered range from a worksheet

I have a UserForm listbox where I want to populate a filtered range (only visible cells) from an excel Worksheet. Problem comes whith this sentence: userForm.listBox.RowSource = dataRng.Address where dataRng is the Range of visible cells (Set dataRng = sht.Range(Cells(startRow, 1), Cells(lastRow, 4)).SpecialCells(xlCellTypeVisible)).

I tried to get the visible range with a dynamic array too (MyArray(i, j) = dataRng.Cells(i, j).Value) and then populate listbox with it, but without succes (here comes header issues but seems the most efficient and fastest solution).

I found this unanswered question from some years ago but I'm looking for a better solution.

 Private Sub listBox_Change()
    Dim startRow,lastRow As Integer
    Dim sht As Worksheet
   'Dim MyArray As Variant 'variant, receives one based 2-dim data field array

        Set sht = Worksheets("SheetName")
        Call filterData(sht) 'filter data in SheetName
        startRow = sht.Cells(Rows.Count, 1).End(xlDown).Row 'get initial row of filtered range
        lastRow = sht.Cells(Rows.Count, 1).End(xlUp).Row 'get last row of filtered range
        Set dataRng = sht.Range(Cells(startRow, 1), Cells(lastRow, 4)).SpecialCells(xlCellTypeVisible) 'get range of visible cells in SheetName

        With userForm.listBox

            .ColumnCount = 4
            .ColumnWidths = "90;90;0;90"
            .RowSource = dataRng.Address

    '    For i = startRow To lastRow
    '        For j = 1 To 3
    '            MyArray(i, j) = dataRng.Cells(i, j).Value
    '        Next j
    '    Next i

        End With

    End Sub

Run-time error 380: Could not set the RowSource property. Invalid property value.

Error message

Upvotes: 0

Views: 2013

Answers (1)

Cyril
Cyril

Reputation: 6829

Qualify the full line...

Set dataRng = sht.Range(Cells(startRow, 1), Cells(lastRow, 4)).SpecialCells(xlCellTypeVisible)

Above does not qualify the Cells() ranges... should be:

Set dataRng = sht.Range(sht.Cells(startRow, 1), sht.Cells(lastRow, 4)).SpecialCells(xlCellTypeVisible)

Same goes for your startrow and lastrow... sht.Rows.Count

Edit1:

Related to the 380, I tend to avoid the .RowSource and append an actual list (particularly when working with non-contiguous ranges), so would loop through the filtered range and, if visible, append to the list.

Untested code to give the concept: 

redim arr(lastrow)
with sht
    for i = startrow to lastrow
        if not .rows(i).entirerow.hidden then 
            arr(n) = .cell(i,1).value 'this appends col 1 (A) cells to a 1-dimensional array
            n = n + 1
        end if
    next i
end with
userform.listbox.list = arr

Note, you can either redim preserve arr(ubound(arr)+1) during your loop to find values or loop through your sized-array after and redim preserve arr(not_is_empty) an array that was dimensioned high to shorten the list to a more appropriate count.

Upvotes: 0

Related Questions