VBA Code Hide/Unhide Rows

tlc53

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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 ?
 
Upvote 0
"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
 
Upvote 0
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.

Code: 
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
 
Last edited:
Upvote 0
You subject title says:
Hide/Unhide Rows

But I see nothing in your script about unhiding rows.
​
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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".​
 
Upvote 0
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
 
Upvote 0
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
Code: 
UsedRange.EntireRow.Hidden = False
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
115
John_w
J
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
403
kevin9999
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
931
dmt32
D
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
423
kevin9999
K
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
361
NoSparks
N

Forum statistics

Threads
1,214,823
Messages
6,121,777
Members
449,049
Latest member
greyangel23

Share this page

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