Spreadsheet Operating Slowly - Improve VBA Coding?

tlc53

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!


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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA macro to search each sheet cell for specific text and print to PDF when true
Replies
6
Views
1K
camerong
C
M
  • Solved
how to combine two vba code?
Replies
4
Views
683
mychi11
M
M
  • Solved
My code was working 2 days back but now my excel just crashes. Thank for any help in Advance!
Replies
9
Views
490
Alex Blakenburg
A
tlc53
  • Question Question
VBA Code Hide/Unhide Rows
2
Replies
17
Views
2K
tlc53
tlc53
L
  • Question Question
Multiple subs in VBA Code ?
Replies
5
Views
3K
Fluff
Fluff

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

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