filter out rows based in drop down field

Occasional Contributor

I am looking to use macro's to create a function that will hide selected rows in a sheet based on the entry of a drop down field, I haven't really had much experience with this sort of thing before.


The solution will be used in an excel dashboard tracking various metrics, and having this automatic filter will make using the workbook easier.


I've included a document which has an example of what I am working with and will hopefully help with a solution..

3 Replies


See the attached version - it's now a .xlsm workbook since it contains VBA code.

To inspect the code, right-click the sheet tab and select 'View Code' from the context menu.

I have copied the code to my real world example, changed the cell referencing the drop down list and receive the following message;

showalldata method of worksheet class failed

Any ideas?


Does it work better if you change the code in the worksheet module to

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ExitHere
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("A2").Value = "" Then
            If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
            Range("A5").CurrentRegion.AutoFilter Field:=2, Criteria1:=Range("A2").Value
        End If
    End If
    Application.EnableEvents = True
End Sub