Populate listbox with filtered range

Hello!

I've seen some threads about this but i can't get the code work.

This is the best code I've found for my situation:

Private Sub CommandButton4_Click()


    Dim sCriteria As String, rngList As Range, sRngAddress As String
     
    sCriteria = Me.cboItem2.Value
     
    With ActiveSheet
        .AutoFilterMode = False
        .Range("A1:H" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=5, Criteria1:=sCriteria
         Set rngList = .Range("A2:H" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
 '       Sheet3.Cells.ClearContents
 '       rngList.Copy Sheet3.Range("A2")
 '       sRngAddress = "SHeet3!" & .Range("A2").SpecialCells(xlCellTypeVisible)
        
    End With
    
'    With Me.ListBox1
'        .RowSource = sRngAddress
'    End With


'    Set rngList = Nothing


    Me.ListBox1.List = rngList.Value

but I dont want to copy and paste, that's why I comment that lines. Just filter and show in the listbox, all in same sheet.

Actually, with the above code I see in the listbox only the first filtered row.

EDIT: Problem of only showing 1st row might be because when you have a filter, the range is divided in area and only the first one is passed?

I also have seen some comments about transposing the range to create a vector and pass it to the listbox?

thanks!!

Re: Populate listbox with filtered range

jaslake, thanks for your time!

unfortunately I can't test your code until tomorrow, I will edit and comment as soon as possible.

thanks!!

Re: Populate listbox with filtered range

hello again jaslake,

this is what I adapted from your code:

Private Sub CommandButton6_Click()


Dim rng As Range
Dim LR As Long




LR = Cells(Rows.Count, "A").End(xlUp).Row




Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
With ListBox1
    .ColumnCount = 7
    For Each Cel1 In rng
        .AddItem CStr(Cel1.Value)
        .List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
        .List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
        .List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
        .List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
        .List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
        .List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
        .List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value




    Next Cel1
End With




End Sub

this is giving me Error: reference not valid in the line

Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)

tried also with range("A2:H") but error too.

EDIT: I tried removing the point (.) before Range and now it works perfect!! And why doesnt work with the point? I have another line very similar and with .range and it works (bad, but works).

Another question...why If you have 10 columns, listcount is only 9?

And last..why is it working without declare "cel1"?

thank you very much jaslake!

Re: Populate listbox with filtered range

Again, thanks for the quick and good answers!

I understood everything, except for the List Count. In your code, your first additem line is (.ListCount -1, """1"""). Why this 1 is not a 0?

And just to make sure, cel1 should be declared as range?

thanks again sir!

Re: Populate listbox with filtered range

ohh I see..wasn't counting the (0) first line!

Now everything is OK!

edit: sorry, just saw your previous post. What difference makes adding sheets("sheet1")?

Thank you so much for all your help!!

Re: Populate listbox with filtered range

Ahá I got it, cool! Thanks very much for all your help and suppor jaslake!

Have a nice day!