VBA to Automatically Hide/Unhide Dynamic Range based Upon Cell Value

[franswa3434]

I have a dropdown selection on A1 with the numbers 1-20.

At the same time, I have rows 2-21 hidden.

I'm looking for a VBA that if they change the dropdown from blank to 5, then it will unhide rows 2-6 (5 rows), then if I change it to 13, it will unhide rows 2-14 (13 rows). And if that isnt enough, if they go from 13 to 10, it will hide rows 12-14.

Is this something I can do?

Thank you for your help!

 

[Micron]

Some of that doesn't makes sense to me.
Blank to 5, unhide 2 to 6. That I understand.
Change 5 to 13, unhide 2 to 14 (notwithstanding that 2 to 6 are already visible). That I understand.
Change 13 to 10 and hide rows 12 to 14. That I don't understand. Based on the first 2 examples, the logical outcome if they went from blank to 10 would be to unhide rows 2 to 11, so why hide 12 to 14 is a bit of a mystery. What if the value was 13 and selected 9? Maybe you meant hide 2 to 14 if the selected value is anything less than what it was before. Or maybe it has to be exactly a change from 13 to 10.

When developing rules based outcomes it is best not to frame it with specifics unless those specifics are hard rules. Whether or not you can do it, no one can say but it's possible to hide/unhide rows based on a drop down selection.

 

[kvsrinivasamurthy]

Worksheet event is used.
If A1=0 or blank all 2:21 will be hidden. For other numbers as per your condition.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Rows("2:21").Hidden = True
    If Target > 0 Then
    Rows("2:" & 2 + Target - 1).Hidden = False
    End If
Application.EnableEvents = True
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Select worksheet in drop down instead of General
Paste the code
Close the VB window.
Save the file as .xlsm

 

[franswa3434]

kvsrinivasamurthy, this does exactly what I am looking for. Thank you for your help!​