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!

 

[tlc53]

It's a drop-down list and all the options are:
Please Select
1
2
3
4
5
6
7
8
9
10

I see what you're getting at regarding no reference to hiding rows. Perhaps I need to look at this.

As you can see there's a lot going on in this sheet and it's only going to get bigger.

I tried removing all my other code and just putting yours in and it kind of worked. Selecting "1" seemed to work however, I couldn't then select "2" for example and I was stuck looking at "1" (again, may need to look at unhide).

One problem that I have noticed, where some of my VBA codes look at a range, if you try to add data near that range it goes extremely slow! For example, one field is enter date and Excel should have no reason to think about it, it's just a basic data entry however it's acting like it's performing a task. Do you know why I would be experiencing this problem? (I appreciate it's a little off topic and it may have something to do with my hide/show five codes).

Thank you.

 

[My Aswer Is This]

You said:

I couldn't then select "2

Why were you not able to enter 2
Or select 2

Was the row now hidden?

​

 

[tlc53]

Sorry, I compiled a reply and then lost it somehow.

The dropdown list options are;
Please Select
1
2
3
4
5
6
7
8
9
10

I see what you're getting at regarding no reference to hiding rows. I tried popping in Michael's suggestion above your code but still no luck.

I'm obviously making a real balls of this and I don't want to waste everyone's time. I'll spend some time reading over your comments again and trying to alter the code and see if I can get it to work.

One problem I am noticing, where my VBA code refers to a range (specifically show/hide Five) if I input data near there, Excel acts as if it is performing an action and runs really slowly, even though it should only be a simple date entry (date for example). Sorry it's a little off topic, but any idea what would be causing this? I have a lot more VBA codes to add but it will be useless if it runs incredibly slowly.

Thank you!

 

[My Aswer Is This]

Are you wanting to toggle these ranges from hidden to unhidden.

So if you enter 2 the range is hidden but then if you enter 2 again the range is unhidden.

This can be done.
It's called toggle

 

[My Aswer Is This]

It's best when trying new code to run it on a sheet with no other code and see if it works the way you want.

Then add it to the sheet with any other code. But realize some code may interfere with other code depending on what it is.

You do have a lot of code in this sheet.

 

[tlc53]

My sheet begins with information about the client for the Bank Reconiliation.
Client Name
Balance Date
No. of Bank Accounts

No. of Bank Accounts defaults to "Please Select" and when you select the down arrow, you can select how many accounts the client has got between 1 and 10. If you chose 5 bank accounts, 5 tables below will show for you to enter the closing bank balance, unpresented cheques, outstanding deposits (if any - yes/no prompts show/hide further input lines) and gives you the revised closing balance of the bank account. As you've selected 5, you need to fill in the information for all of the 5 bank accounts separately under each table. However, perhaps you got it wrong, and actually there were only 4 bank accounts, you should be able to select 4 and the 5th table hides again.

 

[My Aswer Is This]

A lot of people say Table when they mean sheet.

So are you saying for example if you enter a 2 you want a certain Table Hidden.
Like if I select 2 I want all the rows in a table named "David" Hidden

If that is what you want we can write a script to do that and do not need the Rows spelled out

I would write a script something like this:

It would toggle the Table form visible to not visible.

In this example the Table is named Mary

Sub Hide_Table()
'Modified  10/17/2018  11:52:29 PM  EDT
    With ActiveSheet.ListObjects("Mary").Range
        .Rows.Hidden = Not .Rows.Hidden
    End With
Application.ScreenUpdating = True
End Sub

 

[tlc53]

Thanks! Food for thought.
I'm going to have to start from the drawing board me thinks.