Filtering using a listbox across multiple columns

[Idiot]

Hey guys, I found something on these forums that was very close to what I need to do now. I didn't know whether to post there or start a new thread. So, I went with my gut and started a new one.

I've attached the example excel sheet that I'm using with my "progress". And the excel sheet I modified came from this thread: http://www.vbaexpress.com/forum/show...ter+with+macro

The attached is a modified version of post #10 of that thread.

What I want to do, in this example excel sheet, is to filter, not JUST by the brand column, but through multiple columns. The attached excel sheet shows that I was able to populate the list with more than one column, but I'm unable to figure out how to make it work. For example, in the attached spreadsheet, I don't know how I would go about filtering JUST "Due Diligence" and the year "2008" using the list box.

Essentially, the end result would be able to narrow down everything by it's specific column. I want to be able to easily modify the code for other excel sheets that have a similar layout

I don't THINK there is much left to do, but as usual I'm stumped - wish I was smart.

[mdmackillop]

Try
[vba]
Private Sub CommandButton1_Click()
Dim i As Long, Crit As Long
Dim f As Boolean

If Not ActiveSheet.Filtermode Then ActiveSheet.ShowAllData
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
f = True
If IsNumeric(Me.ListBox1.List(i)) Then
Cells(1, 9) = "Year"
Cells(2, 9) = Me.ListBox1.List(i)
Else
Cells(1, 10) = "Brand"
Cells(2, 10) = Me.ListBox1.List(i)
End If
End If
Next
If Not f Then
MsgBox "Select items to filter"
Exit Sub
End If
Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("CritRange"), Unique:=False
Range("CritRange").ClearContents
End Sub

[/vba]

and

CritRange =OFFSET(Foglio1!$I$1,0,0,COUNTA(Foglio1!$I:$I),COUNTA(Foglio1!$1:$1)-6)

[Idiot]

Try
[vba]
Private Sub CommandButton1_Click()
Dim i As Long, Crit As Long
Dim f As Boolean

If Not ActiveSheet.Filtermode Then ActiveSheet.ShowAllData
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
f = True
If IsNumeric(Me.ListBox1.List(i)) Then
Cells(1, 9) = "Year"
Cells(2, 9) = Me.ListBox1.List(i)
Else
Cells(1, 10) = "Brand"
Cells(2, 10) = Me.ListBox1.List(i)
End If
End If
Next
If Not f Then
MsgBox "Select items to filter"
Exit Sub
End If
Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("CritRange"), Unique:=False
Range("CritRange").ClearContents
End Sub

[/vba]
and

CritRange =OFFSET(Foglio1!$I$1,0,0,COUNTA(Foglio1!$I:$I),COUNTA(Foglio1!$1:$1)-6)

Hope you had a good holiday weekend and thanks for taken the time to help me...again

I tried this out and I get a "Run-time error '1004': Application-defined or object-defined error" when i run the macro from the VBA run button, and a "Run-time error '1004': ShowAllData method of Worksheet class failed" when I try to use the filter button on the worksheet. When I hit debug, the "ActiveSheet.ShowAllData" in the line "If Not ActiveSheet.FilterMode Then ActiveSheet.ShowAllData" is highlighted.

Did I do something stupid?

[mdmackillop]

Did I do something stupid?

No, I did that. The Not is wrong.

[Idiot]

No, I did that. The Not is wrong.

At first I was like AWESOME!!!, but then I found problems

It appears to filter correctly if I select either JUST the year and then click filter, or if I click a year and then a brand, and then click filter.

But, if I click just the brand, I'll either get an error or it won't filter at all.

I did a little more testing, it appears that if I:
1. Select a brand and click filter (nothing get's filtered)
2. Select clear filter.
3. Select just a year and click filter.

It will filter the brand that I selected in step one for the year that i selected in step three instead of showing all of the results for just that year.

[khushii]

But, if I click just the brand, I'll either get an error or it won't filter at all. .

Hi, I m new member of this Xcellent Community..

I have gone through the workbook..n did debbugging..

I found is that the "CritRange" is not working properly..
when Year is selected and even when brand n year is selected the offset reference calculation is correct..But when only Brand is selected the returned reference is not correct..

So if CritRange is changed...Problem will b solved..Guess so?

[mdmackillop]

Correct.
My solutionm answered this point

how I would go about filtering JUST "Due Diligence" and the year "2008" using the list box

For single filters or multiple filters, the placement of the data in CritRange needs to be adjusted; or for single filters, a simple AutoFilter can be used.
I think the single listbox for multiple column filter is the wrong approach. A simple userform would be better.

[khushii]

I think the single listbox for multiple column filter is the wrong approach. A simple userform would be better.

hey MD..its d correct solution u have provided...

I have changed the Range n did some modification on code..
Now it is working fine..hopefully

[Idiot]

hey MD..its d correct solution u have provided...

I have changed the Range n did some modification on code..
Now it is working fine..hopefully

AHHH THANK YOU SOO MUCH!!

Just two, hopefully tiny, things:

The first thing is that I notice that I can't filter two or more things from the same column. So if I select, just for example, "Due Diligence" and "Prog. Arch.", only the last clicked will show instead of both.

The other thing, how would I go about adding another column to the filter? Reason I ask is because I have some similar excel sheets, but require more columns to filter. I just want to learn how to do that so I don't have bug you guys all the time. I know how to populate the list and I also know how to set the range for the column I want, I just don't know how to make the actual filter work.


You guys are awesome!!!! I love these forums.

I'm really sorry I'm such a slow learner.

[Idiot]

Correct.
My solutionm answered this point

For single filters or multiple filters, the placement of the data in CritRange needs to be adjusted; or for single filters, a simple AutoFilter can be used.
I think the single listbox for multiple column filter is the wrong approach. A simple userform would be better.

User forms are beyond my comprehension though. I have no idea how to set that up and I don't want to abuse these forums asking how to do it. Then again, I guess I'm already doing it. I know these forums are here for help, but I really need to learn everything from scratch.

[mdmackillop]

A userform example

[Idiot]

A userform example

This is great, I just kind of need a tutorial with this.

I know this is just an example, but I'm really slow. Also, is there a way to fix the way it filters. For example, add 2008, 2010, and Due Diligence to the criteria. Is it possible to show just the overlap? - if that make sense

Sorry if I'm being a pain. I feel that we're extremely close.

My wife figured out how to add to the user form, but the filter isn't working right. Perhaps I should wait on asking for help for that though.

[mdmackillop]

You can only filter with advanced filter. based on the Criteria entered into the grid. I don't understand "overlap", so can you provide an output example with further explanation.

[Idiot]

You can only filter with advanced filter. based on the Criteria entered into the grid. I don't understand "overlap", so can you provide an output example with further explanation.

Oh sorry, by overlap I mean like, don't display results that haven't been added to the criteria. I had a picture in my head that would display the results of one criteria, another picture that would display the results of another and the "overlap" would only be what the two pictures (in my head) had in common.


For example:

Add 2008, 2010, and Due Diligence to the criteria, and click filter:

The result will show some results for 2008 that include Due Diligence, Prog.Impianti, and Prog. Arch. It will show some of 2009 that are only Due Diligence. And finally, there will be 2010 results that show Energie Rinnovalbili, Budgeting, and Prog. Impianti, but no due diligence.

What technically should have happened, in this example, was only display the results for Due Diligence for 2008 and 2010.

2009, Prog.Impianti, Prog. Arch, Energie Rinnovalbili, and Budgeting, shouldn't have been listed.

Sorry for the miscommunication, I just moved into a brand new condo and they're still doing construction in the units around me. The sounds and smells are maddening

[Idiot]

Apparently, my wife figured it out, which further proves that I'm an idiot. I was just first adding criteria A with criteria B, then just adding criteria A by itself to finally clicking Filter. What I should have done was criteria A with criteria B, then criteria A with criteria C. So technically this part is solved.

I want to thank you again for helping me. I really appreciate it all. I'll mark this as solved just as soon as I make sure I don't have any additional questions.

Sorry for all the bother and thank you again!

[Idiot]

Ok, Just thought of one question.

Since I'll be using this for other excel sheets I'll be using a column that will have just numbers that will represent the number of years of a person's experience. since the numbers are so varied, is there a way to make it look for a range in the user form? Like in the list have a 0-2 years, 3-5, 7-8, and so on? Is that difficult to set up?

[mdmackillop]

Check out Advanced Filter help. You'll see need two Age columns. Age >=0 and Age <3 etc.
Set up your advanced filter manually top check it returns the correct result. You then just need to place userfom data etc. in the correct fields.

You might want to consider using Data Validation in the Criteria cells. It may give you a simpler and more flexible solution.

[Idiot]

Check out Advanced Filter help. You'll see need two Age columns. Age >=0 and Age <3 etc.
Set up your advanced filter manually top check it returns the correct result. You then just need to place userfom data etc. in the correct fields.

You might want to consider using Data Validation in the Criteria cells. It may give you a simpler and more flexible solution.

OK Thank you, I think I understand. Mind if I come back and ask if I can't figure it out? I wish I could just get excel to understand the term "0-2", "2-5", "5-8", etc etc, put that down in a column as the search criteria and then have it work.