VBA to hide and unhide rows in Excel based on a value

[malrich]

Hello all,

I've been finding past forum threads useful to learn from others' experiences as I get the hang of Excel. Maybe someone can help me with a question of my own?

I have a drop-down list in A3 of my Excel spreadsheet (version 2010). I'd like my user to be able to select an option from the drop-down list and it automatically hide/unhide rows below, based on their values in cells A5:A100. However, the values in cells A5:A100 are subject to change, so I can't use a formula that says, for example, "hide rows A5:A10 if the user selects "Option A" from the drop-down menu."

Is there any kind of VBA or event code I could use that would compare the value in A3 to the values in A5:A100 and hide all of the rows that do not match?

I hope this makes sense. Thank you for your consideration!

 

[HackSlash]

Yeah, but you're asking for a complete solution. Not help with your existing solution. I think people will be more helpful if you start by writing some code and testing some things out.

I'll give you a starting point:

1. You need a loop that looks at every row you want to evaluate

For evalRow = 5 to 100

Next evalRow

2. Then you want to do the evaluation inside that loop

If ActiveSheet.Range("A" & evalRow).Value = ActiveSheet.Range("A3").Value Then ActiveSheet.Rows(evalRow).EntireRow.Hidden = True

3. This needs to run when cell A3 is changed...

To do this you need to open VBA with ALT+F11 and "View Code" of the sheet you are evaluating

Then make a worksheet change event and check if the cell A3 was changed

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dropDownCell As Range
    Set dropDownCell = ActiveSheet.Range("A3")
    
    If Not Application.Intersect(dropDownCell, Range(Target.Address)) Is Nothing Then
        For evalRow = 5 To 100
            If ActiveSheet.Range("A" & evalRow).Value = ActiveSheet.Range("A3").Value Then ActiveSheet.Rows(evalRow).EntireRow.Hidden = True
        Next evalRow
    End If
End Sub

Play around with that and come back if you have any questions

 

[malrich]

Thanks, Hackslash, for helping me get started in the right direction. Thanks, too, for the advice on how best to use the forum and respect members' time by giving them something to comment on.

As you gathered, I'm very new to VBA so I need more time to work with the tips you gave me before I can fully apply them, but you've saved me hours of trial and error, I'm sure. Cheers!