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

malrich

New Member
Joined
Feb 26, 2018
Messages
2
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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

Code:
For evalRow = 5 to 100

Next evalRow

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

Code:
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

Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top