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,
As a very first step, you could review following guess :

Sub FilterMacro()
    Range("$A$20:$A$40").AutoFilter Field:=1, Criteria1:="=" & Left(Range("D16"),3), Operator:=xlOr, Criteria2:="=Man"
End Sub

 

[Geordiegirl83]

Hi,
As a very first step, you could review following guess :

Sub FilterMacro()
    Range("$A$20:$A$40").AutoFilter Field:=1, Criteria1:="=" & Left(Range("D16"),3), Operator:=xlOr, Criteria2:="=Man"
End Sub

Thank you, but that doesn't seem to change anything when i select options on the filter. :/ do i need to 'run' this each time i make an option change on the drop down?

I was hoping for this to auto change with the menu change ... this is what i have so far but seems to want my options as tables of information rather from rows of information.... But i'm not sure what needs to change to make it work.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OptionType As Range
Set OptionType = Range("D16")

If Intersect(Target, OptionType) Is Nothing Then Exit Sub

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range

'add as many options as you require
Dim FindHdg1 As Range
Dim FindHdg2 As Range
Dim FindHdg3 As Range
Dim FindHdg4 As Range

Set FindHdg1 = Cells.Find("Man")
Set FindHdg2 = Cells.Find("Des")
Set FindHdg3 = Cells.Find("Ess")
Set FindHdg4 = Cells.Find("Gen")

Dim RowsToHide As Range
Set RowsToHide = Range("A21:A500")


Select Case OptionType
Case Is = "All"
Cells.EntireRow.Hidden = False

Case Is = "Mandatory"
Cells.EntireRow.Hidden = False
Set Rng1 = FindHdg1.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng1.EntireRow.Hidden = False

Case Is = "Desirable"
Cells.EntireRow.Hidden = False
Set Rng2 = FindHdg2.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng2.EntireRow.Hidden = False

Case Is = "Essential"
Cells.EntireRow.Hidden = False
Set Rng3 = FindHdg3.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng3.EntireRow.Hidden = False

Case Is = "General"
Cells.EntireRow.Hidden = False
Set Rng4 = FindHdg4.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng1.EntireRow.Hidden = False

End Select

End Sub

 

[James006]

For the macro to be automatically launched by your action in cell D16, you do need to add an Event macro$

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$D$16" Then Exit Sub
Application.Run ("FilterMacro")
End Sub

 

[Geordiegirl83]

For the macro to be automatically launched by your action in cell D16, you do need to add an Event macro$

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$D$16" Then Exit Sub
Application.Run ("FilterMacro")
End Sub

Its still not doing anything at all. I have added this into the code box:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address <> "$D$16" Then Exit Sub
Application.Run ("FilterMacro")

Sub FilterMacro()
Range("$A$21:$A$400").AutoFilter Field:=1, Criteria1:="=" & Left(Range("D16"), 3), Operator:=xlOr, Criteria2:="=Core"
End Sub

 

[James006]

OK ...

The Event Macro post #4 needs to be stored in the Sheet1 Module
and
The Filter Macro post #2 needs to be stored in Module1 (Standard Module)

 

[Geordiegirl83]

Thank you so much that almost works perfectly - Thank You

I was hoping you could help 1 final time?:

1)Is there a way to add the 'All' drop down list option, to show everything when selected.

2) I also have subheadings throughout the table that i would like to remain through the filter process. These are cells A27 & A36, but this may change as the data changes. These have blank/no text in col. A if that helps?

 

[James006]

You are welcome

Glad to hear you have managed to solve your problem

Amended macro to handle the case 'All'

Sub FilterMacro2()
    If Range("D16").Value <> "All" Then
        Range("$A$20:$A$40").AutoFilter Field:=1, Criteria1:="=" & Left(Range("D16"), 3), Operator:=xlOr, Criteria2:="=Man"
    Else
        Range("$A$20:$A$40").AutoFilter Field:=1
    End If
End Sub

 

[Geordiegirl83]

You are welcome

Glad to hear you have managed to solve your problem

Amended macro to handle the case 'All'

Sub FilterMacro2()
    If Range("D16").Value <> "All" Then
        Range("$A$20:$A$40").AutoFilter Field:=1, Criteria1:="=" & Left(Range("D16"), 3), Operator:=xlOr, Criteria2:="=Man"
    Else
        Range("$A$20:$A$40").AutoFilter Field:=1
    End If
End Sub

Thank you so much You are a star.

I have it working perfectly now. I have added the work 'Man' and coloured the text the same as the subheading row so those now stay in place too

 

[Geordiegirl83]

Just me again ... Is there a way to add a secondary filter to the Marco/VBA?

I would like Col. B to have more detail of coverage eg Priority 1, Priority 2 & Priority 3 (with 1 always being viable again) I have tried to replicate the above to be a change2 worksheet and add an extra module too, but I just cant get in to work:

Eg
Module 2 - Macro script as recommended above

Module 3:
Sub FilterMacro2()
If Range("E15").Value <> "All" Then
Range("$B$20:$B$200").AutoFilter Field:=1, Criteria1:="=" & Range("E15"), Operator:=xlOr, Criteria2:="=Priority 1"
Else
Range("$B$20:$B$200").AutoFilter Field:=1
End If
End Sub
______________________________________________________________________________
VBA on worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address <> "$D$16" Then Exit Sub
Application.Run ("FilterMacro")

End Sub
____________________________________________________________________________________
Private Sub Worksheet_Change2(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address <> "$E$15" Then Exit Sub
Application.Run ("FilterMacro2")

End Sub