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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

lrobbo314

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

Watch MrExcel Video

Forum statistics

Threads
1,090,247
Messages
5,413,290
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top