ListBox Range with Filtered Data From a PowerQuery

Allentw821

New Member
Joined
Nov 23, 2017
Messages
4
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.
Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,012
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Nov 23, 2017
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,012
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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:

Code:
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

New Member
Joined
Nov 23, 2017
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,012
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you actually need the table filtered as well as just populating the listbox correctly, then put back the autofilter line.
 

Allentw821

New Member
Joined
Nov 23, 2017
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,012
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,575
Messages
5,469,478
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top