Drop Down Menu to Filter Data

[Chewyhairball]

Hi

Is there a way to use a data validation drop down list to filter results instead of adding a data filter.

The reason I ask is to simplify the filter process for not so computer literate people so they don't have to go through the process of unselecting all the selecting the required field.

I know the data filter is not complicated but a drop down menu would be easier for some folk.

cheers

Rory

 

[fadee2]

hi,
you can use combination of data validation list, some vba code and a command button to make it simpler.

 

[Chewyhairball]

I found this code which does exactly what I want but it doesnt seem to work for me...any ideas why not?
I have the code in the correct place for worksheet change.

thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
 If Range("B2") = "All" Then
  Range("A5").AutoFilter
 Else
  Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2")
 End If
End If
End Sub

 

[Fluff]

In what way doesn't it work?
Also it should be A4 not A5

 

[Chewyhairball]

ADVERTISEMENT

In what way doesn't it work?
Also it should be A4 not A5

It just doesn’t do anything. Doesn’t filter... I tried it with A4 as well but it still didn’t do anything.

 

[Fluff]

Is the code in the correct sheet module?

 

[Chewyhairball]

ADVERTISEMENT

Is the code in the correct sheet module?

Hi. Yes it is in the correct sheet module. It is now working!!! .. I didnt change any of the code and the only thing i did was remove the filters and then add them back in...now its working.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Chewyhairball]

Glad you sorted it & thanks for the feedback.

arrrg! now that its working on my test sheet i transfered it to my spreadsheet and it doesnt work again....there is something odd happening when i apply the filter to cell C6 (this is B5 on my test sheet)
When I select A6 it only adds a filter in this cell as column B is blank. When i try to add a filter to cell C6 it puts the filters in cells D2 and C2. I am assuming this is what is causing it to not work on this sheet. Any ideas what is happening with the filters?

 

[Fluff]

What row does your data start in & what columns are used?