Drop Down Menu to Filter Data

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
319
Office Version
  1. 2019
Platform
  1. Windows
hi,
you can use combination of data validation list, some vba code and a command button to make it simpler.
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97
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
Autofilter from drop down selection



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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
Also it should be A4 not A5
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Is the code in the correct sheet module?
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97

ADVERTISEMENT

Is the code in the correct sheet module?
Hi. Yes it is in the correct sheet module. It is now working!!!o_O .. 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
97
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
What row does your data start in & what columns are used?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top