Hiding Rows using VBA with multiple criteria

[lmjannin]

I apologize if the title of this post is confusing.

I have an Excel sheet that can have up to 10 entries. I have a drop down in AA3 with #'s 1-10. Based on the selection in that box, I need a certain number of rows to be hidden. If 1 is selected, I need a certain set of rows, if 2 is selected, I need a larger set of rows, etc.

I have written this code, and I think there is some sort of error with my syntax. Should there be an OR? When I utilize the code as it is, the hidden rows populate correctly for Range("AA3") = "5", which is the last If statement. It is disregarding what I am trying to do with 1, 2, 3, 4.

If you can't tell, I am pretty new at this...any help is greatly appreciated.

Thanks,


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("AA3") = "1" Then
Rows("45:161").EntireRow.Hidden = True
Else
Rows("45:161").EntireRow.Hidden = False
End If

If Range("AA3") = "2" Then
Rows("58:161").EntireRow.Hidden = True
Else
Rows("58:161").EntireRow.Hidden = False
End If


If Range("AA3") = "3" Then
Rows("71:161").EntireRow.Hidden = True
Else
Rows("71:161").EntireRow.Hidden = False
End If

If Range("AA3") = "4" Then
Rows("84:161").EntireRow.Hidden = True
Else
Rows("84:161").EntireRow.Hidden = False
End If

If Range("AA3") = "5" Then
Rows("97:161").EntireRow.Hidden = True
Else
Rows("97:161").EntireRow.Hidden = False
End If


End Sub

 

[lrobbo314]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AA3")) Is Nothing Then
    Me.Rows.Hidden = False
    
    Select Case Range("AA3").Value
    
        Case 1
            Me.Rows("45:161").EntireRow.Hidden = True
        Case 2
            Me.Rows("58:161").EntireRow.Hidden = True
        Case 3
            Me.Rows("71:161").EntireRow.Hidden = True
        Case 4
            Me.Rows("84:161").EntireRow.Hidden = True
        Case 5
            Me.Rows("97:161").EntireRow.Hidden = True
        Case Else
            Me.Rows.Hidden = False
    
    End Select
End If
End Sub

 

[lmjannin]

This worked perfectly. Thank you so much for your help!

 

[lrobbo314]

Cool. Thanks for the feedback.