filter listbox based on value in column 5

[wCJanssen]

Hi there,

I have a macro that searches the active worksheet and copies all full and partial matches to a listbox. After this is done, I'd Like to enable users to further filter the results - if a checkbox is selected, only rows in the listbox with "admitted" in column 5 should remain, while all other rows are removed from the listbox (but not from the sheet). I tried the following code, which is not working (it removes all rows from the listbox, not just those with "admitted" in column 5):

Private Sub FilterAdmitted()
Dim n As Integer
For n = Results.ListCount - 1 To 0 Step -1
If Not Results.List.column(5).value = "Admitted" Then
Results.RemoveItem n
End If
Next n
End Sub

Thanks in Advance

 

[MickG]

Hi, Try this:-

Private [COLOR="Navy"]Sub[/COLOR] CheckBox1_Click()
[COLOR="Navy"]Dim[/COLOR] nData, Dn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] nLbData
c = 1
nData = ListBox1.List
ReDim nLbData(1 To UBound(nData), 1 To 5)
[COLOR="Navy"]For[/COLOR] Dn = 0 To UBound(nData, 1)
    [COLOR="Navy"]If[/COLOR] nData(Dn, 4) = "Admitted" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] Ac = 0 To UBound(nData, 2)
            nLbData(c, Ac + 1) = nData(Dn, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
        c = c + 1
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Dn
ListBox1.List = nLbData
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mikerickson]

Both indices of a listsbox's .List property are 0 based.

If Not Results.List(n,4) = "Admitted" Then

 

[MickG]

Or based on your original Code and a push from Mike this seems to work.

Private [COLOR="Navy"]Sub[/COLOR] CheckBox2_Click()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 0 To ListBox1.ListCount - 1
    [COLOR="Navy"]If[/COLOR] Not ListBox1.List(n, 4) = "Admitted" [COLOR="Navy"]Then[/COLOR]
        ListBox1.RemoveItem n
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mikerickson]

I would loop with Step -1

 

[wCJanssen]

Thanks a lot to both of you, that last code does exactly what I had in mind.

 

[wCJanssen]

Or based on your original Code and a push from Mike this seems to work.

Private [COLOR="Navy"]Sub[/COLOR] CheckBox2_Click()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 0 To ListBox1.ListCount - 1
    [COLOR="Navy"]If[/COLOR] Not ListBox1.List(n, 4) = "Admitted" [COLOR="Navy"]Then[/COLOR]
        ListBox1.RemoveItem n
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

I ran into a small problem with this code; when I add an entrie to the database, the macro starts removing the wrong items. Any ideas on how this can be solved?

 

[MickG]

Hi, I'm not sure if this is your problem, but as Mike suggested, as with sheet lists its better to remove items from the bottom up. My initial trial didn't seem throw any problems up but, further trials have, so I've altered the code as below. If you still have the problem perhaps you could show the code that fills the listbox.

Private [COLOR="Navy"]Sub[/COLOR] CheckBox2_Click()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] n = ListBox1.ListCount - 1 To 0 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Not ListBox1.List(n, 4) = "Admitted" [COLOR="Navy"]Then[/COLOR]
        ListBox1.RemoveItem n
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[wCJanssen]

that did the trick; thanks again.