Spreadsheet Operating Slowly - Improve VBA Coding?

tlc53

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


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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
Maybe this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 application.screenupdating=false 

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
 application.screenupdating=true   
    
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,872
Office Version
2013
Platform
Windows
I'd probably add this as well

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 with application
.screenupdating=false 
.enablevents=false  
end with 
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
 with application
.screenupdating=true   
.enablevents=True   
end with 
End Sub
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
That's great! Thank you!
Instead of taking 6 counts, it's now reduced it to 2.5 counts. Which is a huge improvement.
I removed "enablevents" as it showed it needed debugging but then I realised there was an "e" missing. However, it seems to run slightly faster without "EnableEvents", so I have omitted it anyway.
Thanks again :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,011
Messages
5,466,014
Members
406,461
Latest member
Garrus

This Week's Hot Topics

Top