VBA Code Hide/Unhide Rows

[tlc53]

Hi,

The sheet I am currently working on has numerous hide/unhide functions built into it. I am currently trying to to hide/unhide rows depending on the number of bank accounts selected. However, I haven't been able to get it to work at all!

Can anyone see where I am going wrong?

Private Sub Worksheet_Change(ByVal Target As Range)




If Range("No._Bank_Accounts") = "Please Select" Then
Range("26:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "1" Then
Range("26:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "2" Then
Range("26:65,82:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "3" Then
Range("26:65,82:121,138:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "4" Then
Range("26:65,82:121,138:177,194:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "5" Then
Range("26:65,82:121,138:177,194:233,250:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "6" Then
Range("26:65,82:121,138:177,194:233,250:289,306:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "7" Then
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "8" Then
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "9" Then
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:457,474:569").EntireRow.Hidden = True
End If
If Range("No._Bank_Accounts") = "10" Then
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:457,474:513,530:569").EntireRow.Hidden = True
End If
End Sub

Thank you!

 

[Michael M]

is Range("No._Bank_Accounts") a single cell ?
How is the dat input into Range("No._Bank_Accounts") ?
Are you putting the code in the sheet module ?

 

[tlc53]

"No._Bank_Accounts" refers to a drop-down list option. It was two merged cells but I changed this to just one cell in case it made a difference (it didn't).
I'm putting the data under the sheet. Right click on tab, view code.

When I select an option from the drop-down list, 3 for example, it returns Run-time error '1004': Method 'Range of Object'_Worksheet failed. If I click unbug, it takes me to the first line of the code

 

[My Aswer Is This]

I would try something like this:

Using case:

I did three for you.

I will let you do the remaining ones.
It's a good way to learn

Just keep adding the remaining Case statement.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/17/2018  9:25:19 PM  EDT
If Not Intersect(Target, Range("No._Bank_Accounts")) Is Nothing Then

If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value
    Case "Please Select"
        Range("26:569").EntireRow.Hidden = True
    Case 1
        Range("26:569").EntireRow.Hidden = True
    Case 2
        Range("26:65,82:569").EntireRow.Hidden = True

End Select
End If
End Sub

 

[My Aswer Is This]

You subject title says:

Hide/Unhide Rows

But I see nothing in your script about unhiding rows.
​

 

[tlc53]

I'm all for practicing and learning. I've already learnt quite a bit but still have a long way to go.
You've helped me before
I tried adding the new code and selecting "Please Select", "1" and then I tried "2". It acted as if it was going to do something (screen does that blinky thing) but nothing happened. I'm still learning how to compile all my codes together, so in case I've done something wrong in that area, I thought I would paste the entire sheet for you to have look at. I have put your new revised code at the bottom..



Private Sub Worksheet_Change(ByVal Target As Range)


If Range("Plus_YN_1") = "NO" Then
Range("26:45").EntireRow.Hidden = True
Else
Range("26:33,44:45").EntireRow.Hidden = False
End If


If Range("Less_YN_1") = "NO" Then
Range("46:65").EntireRow.Hidden = True
Else
Range("46:53,64:65").EntireRow.Hidden = False
End If


If Range("Plus_YN_2") = "NO" Then
Range("82:101").EntireRow.Hidden = True
Else
Range("82:90,100:101").EntireRow.Hidden = False
End If


If Range("Less_YN_2") = "NO" Then
Range("102:121").EntireRow.Hidden = True
Else
Range("102:109,120:121").EntireRow.Hidden = False
End If


If Range("Plus_YN_3") = "NO" Then
Range("138:157").EntireRow.Hidden = True
Else
Range("138:145,156:157").EntireRow.Hidden = False
End If


If Range("Less_YN_3") = "NO" Then
Range("158:177").EntireRow.Hidden = True
Else
Range("158:165,176:177").EntireRow.Hidden = False
End If


If Range("Plus_YN_4") = "NO" Then
Range("194:213").EntireRow.Hidden = True
Else
Range("194:201,212:213").EntireRow.Hidden = False
End If


If Range("Less_YN_4") = "NO" Then
Range("214:233").EntireRow.Hidden = True
Else
Range("214:221,232:233").EntireRow.Hidden = False
End If


If Range("Plus_YN_4") = "NO" Then
Range("194:213").EntireRow.Hidden = True
Else
Range("194:201,212:213").EntireRow.Hidden = False
End If


If Range("Less_YN_4") = "NO" Then
Range("214:233").EntireRow.Hidden = True
Else
Range("214:221,232:233").EntireRow.Hidden = False
End If


If Range("Plus_YN_5") = "NO" Then
Range("250:269").EntireRow.Hidden = True
Else
Range("250:257,268:269").EntireRow.Hidden = False
End If


If Range("Less_YN_5") = "NO" Then
Range("270:289").EntireRow.Hidden = True
Else
Range("270:277,288:289").EntireRow.Hidden = False
End If



If Range("Plus_YN_6") = "NO" Then
Range("306:325").EntireRow.Hidden = True
Else
Range("306:313,324:325").EntireRow.Hidden = False
End If


If Range("Less_YN_6") = "NO" Then
Range("326:345").EntireRow.Hidden = True
Else
Range("326:333,344:345").EntireRow.Hidden = False
End If




If Range("Plus_YN_7") = "NO" Then
Range("362:381").EntireRow.Hidden = True
Else
Range("362:369,380:381").EntireRow.Hidden = False
End If


If Range("Less_YN_7") = "NO" Then
Range("382:401").EntireRow.Hidden = True
Else
Range("382:389,400:401").EntireRow.Hidden = False
End If



If Range("Plus_YN_8") = "NO" Then
Range("418:437").EntireRow.Hidden = True
Else
Range("418:425,436:437").EntireRow.Hidden = False
End If


If Range("Less_YN_8") = "NO" Then
Range("438:457").EntireRow.Hidden = True
Else
Range("438:445,456:457").EntireRow.Hidden = False
End If



If Range("Plus_YN_9") = "NO" Then
Range("474:493").EntireRow.Hidden = True
Else
Range("474:481,492:493").EntireRow.Hidden = False
End If


If Range("Less_YN_9") = "NO" Then
Range("494:513").EntireRow.Hidden = True
Else
Range("494:501,512:513").EntireRow.Hidden = False
End If


If Range("Plus_YN_10") = "NO" Then
Range("530:549").EntireRow.Hidden = True
Else
Range("530:537,548:549").EntireRow.Hidden = False
End If


If Range("Less_YN_10") = "NO" Then
Range("550:569").EntireRow.Hidden = True
Else
Range("550:557,568:569").EntireRow.Hidden = False
End If
End Sub




Sub ShowFive()
Dim rng As Range
On Error Resume Next
Set rng = [C34:C43].SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
[C34:C39].EntireRow.Hidden = False
ElseIf rng.Count = 10 Then
Exit Sub
Else: rng(rng.Count + 1, 1).Resize(5).EntireRow.Hidden = False
End If
End Sub




Sub HideFive()
Dim r%
For r = 43 To 34 Step -5
If WorksheetFunction.CountA(Range(Cells(r - 4, "B"), Cells(r, "B"))) <> 0 Then
Exit For
Else
Rows(r - 4 & ":" & r).Hidden = True
End If
Next


End Sub




'Modified 10/17/2018 9:25:19 PM EDT
If Not Intersect(Target, Range("No._Bank_Accounts")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value
Case "Please Select"
Range("26:569").EntireRow.Hidden = True
Case 1
Range("26:569").EntireRow.Hidden = True
Case 2
Range("26:65,82:569").EntireRow.Hidden = True


End Select
End If
End Sub

 

[My Aswer Is This]

You need to only have the code I provided in the sheet.
It appears as if you have all your old code and my code.

Remove your change event code

 

[My Aswer Is This]

Are you entering 1 or 2 or 3

Or are you entering PleaseSelect1

Or PleseSelect2

You said:

I tried adding the new code and selecting "Please Select", "1" and then I tried "2".​

 

[My Aswer Is This]

In case you do not understand case.

Case 1

Means if you enter a 1 into the cell the action below will happen


So if I had

Case 1
Msgbox "Hello"


A Message Box would popup saying "Hello"


If I had:

Case 2
Msgbox "Go Home"


Then a message box would popup saying "Go Home"
If I entered a 2 into the cell

 

[Michael M]

Also, as @MAIT mentioned there is no mention of unhiding the rows.

I'd suggest putting this line of code at the beginning of your macro

UsedRange.EntireRow.Hidden = False