Advanced Filter & ListBox

[Sahak]

Hi all,

I did search on line, but could not find what I need …
Using Excel VBA, I need to get on User Form's listbox advance filtered data form work sheet. I set named range on listbox’s RowSource property, so it shows named range from work sheet, let say data with 10 rows,. But when I filter that range & get less rows, let say 4 rows, the listbox again shows 10 rows, I would like to see on ListBox. 4 rows

Thank you in advance.

 

[My Aswer Is This]

Here is what I do.
I never us row source

I Make myself a Excel Table with only one column of data:
In my example I named my Table "Jack"

The table always expands as I enter more rows in the Table.

And the UserForm listbox loads when UserForm is opened.

Put this script in your UserForm

Private Sub UserForm_Initialize()
'Modified  12/13/2018  3:14:40 PM  EST
ListBox1.List = Range("Jack").Value
End Sub

 

[Kamolga]

For my dropdown lists, I also use tables on a List hidden sheet, in VBA I normally use arrays.
Now if your NamedRange is something you want the user to manipulate, you might want to paste this function in a module

Function FilteredValues(Rng As Range) As Range
Dim Cell As Range
Dim Result As Range
For Each Cell In Rng
    If Cell.EntireRow.Hidden = False Then
        If Result Is Nothing Then
            Set Result = Cell
        Else
            Set Result = Application.Union(Result, Cell)
        End If
    End If
Next
Set [COLOR=#222222][FONT=Verdana]FilteredValues[/FONT][/COLOR]= Result
End Function

You ten define a new name FilteredRange and in source you type

=[COLOR=#222222][FONT=Verdana]FilteredValues(NamedRange)[/FONT][/COLOR]

. You can now refer to FilteredRange in your source

 

[Sahak]

Hi,

Thank you for reply.

My table on worksheet named "Jack" & have 10 rows, after filtering visible rows become 4. I tried your code, it also shows all 10 rows ???

 

[My Aswer Is This]

Sorry. I did not know you meant filtered values.
Also I said name the Table Jack not name the sheet Jack

 

[Sahak]

Hi Kamolga,

Thank you for reply.

My table on worksheet has 10 rows (including titles) & i defined to table name "Jack", I have paste your Function code (without any changes to it) in module & tried in Listbox's RowSource property to enter =FilteredValues(Jack) and it gives an error "Could not set the RowSource property. Invalid property value." where is my mistake?

 

[Sahak]

Sorry, "Jack" is table's name

 

[Kamolga]

You need to go to define name (formula tab), chose a name (e.g. FilteredJack) and type

[B][COLOR=#333333]=FilteredValues(Jack)[/COLOR][/B]

You can now refer to =FilteredJack for a dropdown or =Range("FilteredJack") for a listbox

Note: Jack should be the content of your table (without title). It should refer to something like =Table1[column1]

 

[Sahak]

Dear Kamolga, I totally confused, please see your private message.

 

[Kamolga]

Sorry for confusion, it worked with a standard dropdown list and I thought it would therefore work with a Userform listbox. It does not.

As I said, I would normally work with array in VBA than Range. That means I would get the listbox populated when I activate the userform (Userform1.show)

Private Sub UserForm_Activate()
Dim cell As Range
Dim MyArr  As Variant, i As Long
[COLOR=#006400]' intialize array to high number of elements at start[/COLOR]
ReDim MyArr(0 To 10000)
  [COLOR=#006400]  ' scan each cell in Range "Jack" only on visible cells (cells that are visible after the filter was applied)[/COLOR]
    For Each cell In Range("[COLOR=#0000ff]Jack[/COLOR]").SpecialCells(xlCellTypeVisible)
        MyArr(i) = cell.Value [COLOR=#008000]' read all visible cells to array[/COLOR]
        i = i + 1
    Next cell
    [COLOR=#006400]' reduce array size to populated elements only[/COLOR]
    ReDim Preserve MyArr(0 To i - 1)
    [COLOR=#006400]' populate listbox with array[/COLOR]
    [COLOR=#0000ff]ListBox1[/COLOR].List = MyArr
End Sub