filter listbox based on value in column 5

wCJanssen

New Member
Joined
Feb 22, 2009
Messages
24
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):

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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Try this:-
Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,793
Both indices of a listsbox's .List property are 0 based.

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Or based on your original Code and a push from Mike this seems to work.
Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,793
I would loop with Step -1
 

wCJanssen

New Member
Joined
Feb 22, 2009
Messages
24
Or based on your original Code and a push from Mike this seems to work.
Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
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
 

Forum statistics

Threads
1,082,119
Messages
5,363,275
Members
400,724
Latest member
Angeljurich15

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top