CODEWITHSUNDEEP
excelvba
Nick Fleetwood
Nick Fleetwood

Reputation: 531

Pass Filtered Table data to ListBox

I have a table below:

enter image description here

I'm trying to filter the data and then display the filtered data to a Listbox on a userform. Code so far:

Dim iList As Variant

Sheet1.ListObjects("PostOneTable").Range.AutoFilter Field:=1, Criteria1:=TextBox1.Value & "*"
Sheet1.ListObjects("PostOneTable").Range.AutoFilter Field:=2, Criteria1:=TextBox2.Value & "*"
Sheet1.ListObjects("PostOneTable").Range.AutoFilter Field:=3, Criteria1:=TextBox3.Value & "*"
Sheet1.ListObjects("PostOneTable").Range.AutoFilter Field:=5, Criteria1:=TextBox4.Value & "*"

Dim jList As Range

Set jList = 
Sheet1.ListObjects("PostOneTable").DataBodyRange.SpecialCells(xlCellTypeVisible)

ListBox1.List = jList

However, upon running the code, I run into a few problems. Firstly, the filters don't seem to apply correctly. For example, if I use a string, such as:

Sheet1.ListObjects("PostOneTable").Range.AutoFilter Field:=1, Criteria1:="ryd"

I would expect to only see the fourth item, both in the Table and in the ListBox. However, both are blank.

Second issue is that the code doesn't drive the filtered list to the textbox.

Please note that I am aware of other ways to search, using loops. However, this method is good because the end users will be able to see results of their actions on the form directly in the table.

Thanks!

EDIT: Upon playing with it, I noticed something interesting. If I comment out the section that drives the data to the listbox AND rewrite the filtering lines, the filtering now works.

Upvotes: 2

Views: 2120

Answers (1)

TinMan
TinMan

Reputation: 7759

Range.Value only returns the values from the first area in a range. Range.SpecialCells(xlCellTypeVisible) will return a Range that has an Area for each contiguous block of cells.

ListBox1.List = jList

ListBox1.List will only have the data for the first group of visible rows (the first Are in jList).

You can still use the ListObject to filter the data. You just need to write a function to return the visible data. Which is a trivia task to do.

ListBox1.List = ListObjectVisibleData(Sheet1.ListObjects("PostOneTable"))

Public Function ListObjectVisibleData(ByVal ListObject As ListObject)
    Dim Map As New Collection
    Dim Row As Range
    For Each Row In ListObject.DataBodyRange.Rows
        If Not Row.EntireRow.Hidden Then
            Map.Add Row
        End If
    Next
    
    Dim Results As Variant
    Dim r As Long, c As Long
    
    If Map.Count = 0 Then
        ReDim Results(1 To 1, 1 To ListObject.ListColumns.Count)
    Else
        ReDim Results(1 To Map.Count, 1 To ListObject.ListColumns.Count)
        For Each Row In Map
            r = r + 1
            For c = 1 To UBound(Results, 2)
                Results(r, c) = Row.Cells(1, c).Value
            Next
        Next
    End If
    
    ListObjectVisibleData = Results
End Function

Upvotes: 2

Related Questions