tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi there,
I have the following VBA coding on my spreadsheet which functions as I want it to.
The only problem is, it is quite slow and flickers a lot. Even when data is entered into a basic cell with no VBA coding attached to it, it acts like it is having a good think about it.
Is there anyway I can tidy up my coding to make it work better/faster?
If Case 1 is selected, then all the coding from "Plus_YN_02" down to "Less_YN_10" are not required.
If Case 2 is selected, then all the coding from "Plus_YN_03" down to "Less_YN_10" are not required.
If Case 3 is selected, then all the coding from "Plus_YN_04" down to "Less_YN_10" are not required, and so on.
I'm not sure if it's possible to deactivate/ignore coding depending on the Case number selected. However, I'm not sure if it is this making it super slow.
Can anyone see how this can be improved please??? Thank you!
I have the following VBA coding on my spreadsheet which functions as I want it to.
The only problem is, it is quite slow and flickers a lot. Even when data is entered into a basic cell with no VBA coding attached to it, it acts like it is having a good think about it.
Is there anyway I can tidy up my coding to make it work better/faster?
If Case 1 is selected, then all the coding from "Plus_YN_02" down to "Less_YN_10" are not required.
If Case 2 is selected, then all the coding from "Plus_YN_03" down to "Less_YN_10" are not required.
If Case 3 is selected, then all the coding from "Plus_YN_04" down to "Less_YN_10" are not required, and so on.
I'm not sure if it's possible to deactivate/ignore coding depending on the Case number selected. However, I'm not sure if it is this making it super slow.
Can anyone see how this can be improved please??? Thank you!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
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:569").EntireRow.Hidden = False
Range("26:65,82:569").EntireRow.Hidden = True
Case 3
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:569").EntireRow.Hidden = True
Case 4
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:177,194:569").EntireRow.Hidden = True
Case 5
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:177,194:233,250:569").EntireRow.Hidden = True
Case 6
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:177,194:233,250:289,306:569").EntireRow.Hidden = True
Case 7
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:569").EntireRow.Hidden = True
Case 8
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:569").EntireRow.Hidden = True
Case 9
Range("26:569").EntireRow.Hidden = False
Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:457,474:569").EntireRow.Hidden = True
Case 10
Range("26:569").EntireRow.Hidden = False
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 Select
End If
If Range("Plus_YN_01") = "NO" Then
Range("26:45").EntireRow.Hidden = True
Else
Range("26:33,44:45").EntireRow.Hidden = False
End If
If Range("Less_YN_01") = "NO" Then
Range("46:65").EntireRow.Hidden = True
Else
Range("46:53,64:65").EntireRow.Hidden = False
End If
If Range("Plus_YN_02") = "NO" Then
Range("82:101").EntireRow.Hidden = True
Else
Range("82:90,100:101").EntireRow.Hidden = False
End If
If Range("Less_YN_02") = "NO" Then
Range("102:121").EntireRow.Hidden = True
Else
Range("102:109,120:121").EntireRow.Hidden = False
End If
If Range("Plus_YN_03") = "NO" Then
Range("138:157").EntireRow.Hidden = True
Else
Range("138:145,156:157").EntireRow.Hidden = False
End If
If Range("Less_YN_03") = "NO" Then
Range("158:177").EntireRow.Hidden = True
Else
Range("158:165,176:177").EntireRow.Hidden = False
End If
If Range("Plus_YN_04") = "NO" Then
Range("194:213").EntireRow.Hidden = True
Else
Range("194:201,212:213").EntireRow.Hidden = False
End If
If Range("Less_YN_04") = "NO" Then
Range("214:233").EntireRow.Hidden = True
Else
Range("214:221,232:233").EntireRow.Hidden = False
End If
If Range("Plus_YN_04") = "NO" Then
Range("194:213").EntireRow.Hidden = True
Else
Range("194:201,212:213").EntireRow.Hidden = False
End If
If Range("Less_YN_04") = "NO" Then
Range("214:233").EntireRow.Hidden = True
Else
Range("214:221,232:233").EntireRow.Hidden = False
End If
If Range("Plus_YN_05") = "NO" Then
Range("250:269").EntireRow.Hidden = True
Else
Range("250:257,268:269").EntireRow.Hidden = False
End If
If Range("Less_YN_05") = "NO" Then
Range("270:289").EntireRow.Hidden = True
Else
Range("270:277,288:289").EntireRow.Hidden = False
End If
If Range("Plus_YN_06") = "NO" Then
Range("306:325").EntireRow.Hidden = True
Else
Range("306:313,324:325").EntireRow.Hidden = False
End If
If Range("Less_YN_06") = "NO" Then
Range("326:345").EntireRow.Hidden = True
Else
Range("326:333,344:345").EntireRow.Hidden = False
End If
If Range("Plus_YN_07") = "NO" Then
Range("362:381").EntireRow.Hidden = True
Else
Range("362:369,380:381").EntireRow.Hidden = False
End If
If Range("Less_YN_07") = "NO" Then
Range("382:401").EntireRow.Hidden = True
Else
Range("382:389,400:401").EntireRow.Hidden = False
End If
If Range("Plus_YN_08") = "NO" Then
Range("418:437").EntireRow.Hidden = True
Else
Range("418:425,436:437").EntireRow.Hidden = False
End If
If Range("Less_YN_08") = "NO" Then
Range("438:457").EntireRow.Hidden = True
Else
Range("438:445,456:457").EntireRow.Hidden = False
End If
If Range("Plus_YN_09") = "NO" Then
Range("474:493").EntireRow.Hidden = True
Else
Range("474:481,492:493").EntireRow.Hidden = False
End If
If Range("Less_YN_09") = "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
Dim rng As Range
Set rng = Intersect(Target, [B33:B43,B53:B64,B90:B99,B109:B119,B145:B155,B165:B175,B201:B211,B221:B231,B257:B267,B277:B287,B313:B323,B333:B343,B369:B379,B389:B399,B425:B435,B445:B455,B481:B491,B501:B511,B537:B547,B557:B567])
If Not rng Is Nothing Then rng(2, 1).EntireRow.Hidden = False
End Sub