ListBox Range with Filtered Data From a PowerQuery

[Allentw821]

Hello,

I have a power query table on sheet2 that will have a different range each time the query is ran. I need it to populate a list box on sheet1. Kicker. It also has to be able to populate with filtered data also (filter selection is a combo box on Sheet1 "C26"). I tried a few different ways as shown on previous questions from other users. No success. Thank you.

Private Sub ComboBox1_Change()
 
 
If Range("Sheet1!C26") = "All Areas"Then

 

       Application.Sheets("Sheet2").ListObjects("Files").Range.AutoFilter
    Else
       Application.Sheets("Sheet2").ListObjects("Files").Range.AutoFilterField:=3, Criteria1:=Range("Sheet1!C26")

 

End If
 

With Sheets("Sheet2")
    Dim LR As Long

    Dim rng As Range
 

    LR =Cells(Rows.Count, "A").End(xlUp).Row
    Set rng =Sheets("Sheet2").Range("A1:A" &LR).SpecialCells(xlCellTypeVisible)

End With

    

With Sheet1.ListBox1

    .ListFillRange =(this code is what I need)

    .ListIndex = 0

    .Activate

 

End With
 
End Sub

 

[RoryA]

Welcome to the forum.

Unless your data is sorted by the column you’re filtering, you’ll have to loop through the visible cells and add them to the combo box. You can’t use Listfillrange - unless you copy the filtered data elsewhere, anyway.

 

[Allentw821]

Yea that was my intitial try. I kept hitting a snag and couldn't work it out. I gave up and tried that. I didn't even realize i left the end if in when i posted. I know just enough vba to drive myself crazy just before it works.

I tried to loop throught the cells with specialscells (xlcelltypevisible) and then offset them for the listbox. That code is what I ended up with after giving up.

 

[RoryA]

Do you actually need/want the range on the sheet to be filtered? If not, it would be quicker to just load the whole table in to an array, loop through that checking the third column, and add the necessary items to the combobox. For example:

Private Sub ComboBox1_Change()
    Dim lo As ListObject
    Set lo = Application.Sheets("Sheet2").ListObjects("Files")
    Dim lb As MSForms.ListBox
    Set lb = Sheet1.ListBox1
    lb.ListFillRange = ""
    lb.Clear
    Dim crit As Variant
    crit = Sheets("Sheet1").Range("C26").Value

    lo.Range.AutoFilter

    Dim datain
    datain = lo.ListColumns(1).DataBodyRange.Resize(, 3).Value

    If crit = "All Areas" Then
        lb.List = Application.Index(datain, 0, 1)
    Else
        Dim thecount As Long
        thecount = Application.CountIf(lo.ListColumns(3).Range, crit)
        If thecount > 0 Then
            Dim dataout()
            ReDim dataout(1 To thecount, 1 To 1)
            Dim n As Long, counter As Long
            counter = 1
            For n = LBound(datain) To UBound(datain)
                If datain(n, 3) = crit Then
                    dataout(counter, 1) = datain(n, 1)
                    counter = counter + 1
                End If
            Next
            lb.List = dataout
        End If
    End If

End Sub

 

[Allentw821]

Yea it really needs to filter. I thought about using power query to filter out unwanted data, but I know i would have to adjust it later. To give you an idea, a manufacturing plant sends samples in from area A, B, C, and D. They are tested and put into a dba. The combox lets them filter out samples from other areas, but if something isn't going right, area B needs to see data from area A.

 

[RoryA]

If you actually need the table filtered as well as just populating the listbox correctly, then put back the autofilter line.

 

[Allentw821]

I tried the code without filter. The first column was added to the listbox, but the second column and the headers were gone. The dependents didn't work also, but they had an elements from "files" in there.

 

[RoryA]

You can't have headers unless you use a range, which would mean you'd need to copy the data somewhere else and use that as the source. I'm not sure what the last part of your comment means.