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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top