Filter blank cells based on dropdown

[Fogojam]

I have a spreadsheet used to track employee performance in different disciplines. What I'm trying to accomplish is a drop down menu where based on user selection all blank cells in a given column will be filtered out. As you can see from the screenshot below, the first column contains an analyst name and the following columns each contain a specific discipline. Each row represents a different employee. Not all employees have a score in every discipline. What I'm hoping to accomplish is a drop down box containing each discipline (Biology, Chemistry - CS, Firearms Proc, etc) where when you select Biology for instance, all rows without a score in Biology are filtered out. I know this could be accomplished easily using the basic filter on each column, but I am trying to accomplish it using a drop down box specifically.

I was able to create a drop down menu to filter by employee, but I am really struggling to accomplish this specific task. I appreciate any help.. thank you!

Capture.JPG

[LeoTaxi]

With dynamic named range for dropdown in A1
and this code for button

Please Login or Register  to view this content.

Kind regards
Leo

[Fogojam]

Perfect! Thank you so much!

[Fogojam]

Leo,

When I tried to adapt your code to auto filter the data without having to hit the filter button, I am having trouble. Instead of filtering out the rows WITHOUT data, it is filtering the rows WITH data. So for instance if I select Chemistry from my filter, all rows containing an entry in the chemistry column are removed. Basically the exact opposite of what I want.

Here is the code I entered into the "Worksheet_Change" sub of my sheet.

If Target.Address = "$C$4" Then
With ActiveSheet
mycol = Application.Match(.Range("C4"), .Range("A5", "XDF5"), 0)
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A5", "Z" & lastrow).AutoFilter
.Range("A5", "Z" & lastrow).AutoFilter Field:=mycol, Criteria1:="=***", Operator:=xlAnd
End With
End If

C4 is the location of the dropdown, A5 is my header column and Z is the last column with an entry.

EDIT: You can disregard, I got my desired result by switching it to Criteria1:="<>***"