Hiding Rows using VBA with multiple criteria

lmjannin

New Member
Joined
Oct 12, 2019
Messages
7
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,711
Office Version
365, 2019, 2016
Platform
Windows
Try this.

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,711
Office Version
365, 2019, 2016
Platform
Windows
Cool. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,782
Messages
5,446,471
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top