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:



    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


    Hi ignasis


    Here's some code I have in my toolbox. See if you can adapt it.


    Re: Populate listbox with filtered range


    hello again jaslake,


    this is what I adapted from your code:



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

    Code
    1. 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


    Hi ignasis


    The Dot (.) before Range is used in the With...End With construct like so...


    Quote

    why If you have 10 columns, listcount is only 9


    List Count is 0 based unless you tell the Code differently; so, 0 to 9 will be 10 (in your case 0 to 7 will be 8...Columns A - H).

    Quote

    why is it working without declare "cel1"?


    Because you've not used Option Explicit which forces one to declare all Variables.

    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


    Hi ignasis


    Count 'em...10 items (0 through 9)


    Code
    1. (0) .AddItem CStr(Cel1.Value)
    2. (1) .List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
    3. (2) .List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
    4. (3) .List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
    5. (4) .List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
    6. (5) .List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
    7. (6) .List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
    8. (7) .List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value
    9. (8) .List(.ListCount - 1, 8) = Cel1.Offset(0, 8).Value
    10. (9) .List(.ListCount - 1, 9) = Cel1.Offset(0, 9).Value


    Yes, it's a Range Object.

    Quote

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

    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


    This requires With...End With

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


    This does not

    Code
    1. Set rng = Sheets("Sheet1") .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)