VBA Help - Hide rows based on a drop down list selection

[Geordiegirl83]

Hi All,

I'm a total novice when it comes to VBA and i have been watching a number of tutorials but cant quite get the scripted i need to make my spreadsheet work.

I have a dropped down in cell D16 with various text (all, essential, desirable, mandatory, general)

I have a corresponding table below the menu box where Column A21 - A40 contains abbreviations of those in the menu drop down list (Ess, Des, Man, Gen) but not in any specific order and will duplicate multiple times within the cell range.

I would the table to auto hide any row not corresponding to the option selected in the drop down, but also keeping any that says 'Man' visible at all times.

Any help would be AMAZING! as i have been trying to do this for 2 days :/

Thanks in advance

 

[James006]

Hi,
You cannot have two Worksheet_Change for the same sheet module ... they do need to be combined in ONE Event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
' Combined Event Macro
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E15", "D16")) Is Nothing Then Exit Sub
If Target.Address = "$D$16" Then Application.Run ("FilterMacro")
If Target.Address = "$E$15" Then Application.Run ("FilterMacro2")
End Sub

 

[Geordiegirl83]

Hi,
You cannot have two Worksheet_Change for the same sheet module ... they do need to be combined in ONE Event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
' Combined Event Macro
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E15", "D16")) Is Nothing Then Exit Sub
If Target.Address = "$D$16" Then Application.Run ("FilterMacro")
If Target.Address = "$E$15" Then Application.Run ("FilterMacro2")
End Sub

Thanks James, that seems to work brilliantly.

Is there any way of Making the filer E15 the primary filter and D16 the secondary

Eg: If E15 has priority 1 the options in D16 cant be outside of this filter, as currently D16 overrides and filters on E15 and vise versa.

So if I have a list of Priority 1 options, then I cant see the Essential etc that fall under priority 2 .. if that makes sense

 

[James006]

Glad to hear it is working as expected.

Regarding your latest question, not sure to understand the "priority" question ...

Thanks for clarifying ...

 

[Geordiegirl83]

Hi James,

The best way I can explain : If I use the conventional filter option in the ribbon and filter Col A, I can then only filter Col.B by the already reduced filter options.

The above codes work independently, but if I use the drop list to filter D16 which says "essentials, desirables and mandatory" to 'essentials' it will filter all essentials in Col B - Perfect, but then if I change the filter in E16 with the drop list "Priority 1, Priority 2 & Priority 3" to say 'Priority 1', It will fitler Col A to all this wording but it then un-filters Col. A will get all the options back in A and only B is filtered.

I would like Col. A (E16) to be the primary filter and Col.B (D16) to be the secondary filter

Hope that makes sense

Thanks Mandy

 

[James006]

Hi Mandy,

Thanks for the explanation
Below is your revised FilterMacro :

Sub MyFilterMacro()
' Make sure the Cells are the ones you need  '''''''''''''''''''''
' Make sure your Range Database is adapted to your situation '''''
    If Range("E16").Value <> "All" Then
        'Apply Filter to database filtering Column A using cell E16
        Range("$A$20:$B$200").AutoFilter Field:=1, Criteria1:="=" & Left(Range("E16"), 3), Operator:=xlOr, Criteria2:="=Man"
        'Apply Filter to database filtering Column B using cell D16
        Range("$A$20:$B$200").AutoFilter Field:=2, Criteria1:="=" & Range("D16"), Operator:=xlOr, Criteria2:="=Priority 1"
    Else
        ' Remove all Criteria from the Filtering process, in order to be back to Normal, i.e.
        ' Select all fields both in Column A AND in Column B
        Range("$A$20:$B$200").AutoFilter Field:=1
        Range("$A$20:$B$200").AutoFilter Field:=2
    End If
End Sub

Hope this will meet your expectations

 

[Geordiegirl83]

Hi Mandy,

Thanks for the explanation
Below is your revised FilterMacro :

Sub MyFilterMacro()
' Make sure the Cells are the ones you need  '''''''''''''''''''''
' Make sure your Range Database is adapted to your situation '''''
    If Range("E16").Value <> "All" Then
        'Apply Filter to database filtering Column A using cell E16
        Range("$A$20:$B$200").AutoFilter Field:=1, Criteria1:="=" & Left(Range("E16"), 3), Operator:=xlOr, Criteria2:="=Man"
        'Apply Filter to database filtering Column B using cell D16
        Range("$A$20:$B$200").AutoFilter Field:=2, Criteria1:="=" & Range("D16"), Operator:=xlOr, Criteria2:="=Priority 1"
    Else
        ' Remove all Criteria from the Filtering process, in order to be back to Normal, i.e.
        ' Select all fields both in Column A AND in Column B
        Range("$A$20:$B$200").AutoFilter Field:=1
        Range("$A$20:$B$200").AutoFilter Field:=2
    End If
End Sub

Hope this will meet your expectations

Thank you so so much for your help so far. One very last request (i hope lol) as its not quite working perfectly.

At the moment the filter almost perfect, however if I change D16 I loose all the options in row B with the exception of 'Man' is there anyway to add E16 - All then change if the options are avaiable.

Eg: At present if i select Priority 2 in D16, all the filter options in B (Essential, Mandatory etc) only show Mandatory, which isnt accurate as it should show all categories still. So ideally Id like to see 'ALL' from row B first and then have the option to filter down further in B using the drop list in E16. (if that makes sense)

 

[James006]

Let me see if I can eventually decode your rather circumvoluted request ...

 

[Geordiegirl83]

Thank you so so much for your help so far. One very last request (i hope lol) as its not quite working perfectly.

At the moment the filter almost perfect, however if I change D16 I loose all the options in row B with the exception of 'Man' is there anyway to add E16 - All then change if the options are avaiable.

Eg: At present if i select Priority 2 in D16, all the filter options in B (Essential, Mandatory etc) only show Mandatory, which isnt accurate as it should show all categories still. So ideally Id like to see 'ALL' from row B first and then have the option to filter down further in B using the drop list in E16. (if that makes sense)

I think an easier way to explain what i want could be is that it is in line again with a normal filter

eg: If i filter Col A using D16, Col B - Shows 'ALL' options that align with the filter for A, until a further filter is then added using E16