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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,696
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,696
Office Version
365, 2019, 2016
Platform
Windows
Cool. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,100
Messages
5,412,420
Members
403,426
Latest member
Wally309

This Week's Hot Topics

Top