VBA - Need help running faster/refining code

M

MC71517

New Member
Joined
May 10, 2018
Messages
2
I a very green to VBA (self taught in the last 3 weeks)... I created the below code, which works but is VERY slow.

I am hoping someone can help as I still need to protect/unprotect the worksheets which is only going to further impact performance.

The code has 3 intended results:
1) Reading a matrix I created on the active sheet (O2:P17) with formulas to make all defined sheets visible or very hidden​
(O has sheet name and P has Yes/No - I replaced Worksheet.("O2") with Sheet4. in case a user renames sheets).​
2) Hide rows throughout the workbook dependent on User selected answers. (Alternatively, each row that needs to be hidden has a formulated "Hide" in column A of the specific row. Not sure if there is a way to loop through the entire workbook and hide/unhide dynamically without clicks on the specific worksheet).​
3) Hide Columns in two specific worksheets dependent on User selected answers.​

The code did not have performance issues until I replaced "Sheet Names" with Code Names (i.e. Sheet21.) throughout.

Any assistance would be greatly appreciated.


************ CODE BELOW ******************
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
 
If Range("P2").Value = "Yes" Then
        Worksheets(Range("O2").Value).Visible = True
    Else
        Worksheets(Range("O2").Value).Visible = False
    End If
    If Range("P3").Value = "Yes" Then
        Worksheets(Range("O3").Value).Visible = True
    Else
        Worksheets(Range("O3").Value).Visible = False
    End If
    If Range("P4").Value = "Yes" Then
        Worksheets(Range("O4").Value).Visible = True
    Else
        Worksheets(Range("O4").Value).Visible = False
    End If
    If Range("P5").Value = "Yes" Then
        Worksheets(Range("O5").Value).Visible = True
    Else
        Worksheets(Range("O5").Value).Visible = False
    End If
    If Range("P6").Value = "Yes" Then
        Worksheets(Range("O6").Value).Visible = True
    Else
        Worksheets(Range("O6").Value).Visible = False
    End If
        If Range("P7").Value = "Yes" Then
        Worksheets(Range("O7").Value).Visible = True
    Else
        Worksheets(Range("O7").Value).Visible = False
    End If
    If Range("P8").Value = "Yes" Then
        Worksheets(Range("O8").Value).Visible = True
    Else
        Worksheets(Range("O8").Value).Visible = False
    End If
    If Range("P9").Value = "Yes" Then
        Worksheets(Range("O9").Value).Visible = True
    Else
        Worksheets(Range("O9").Value).Visible = False
    End If
    If Range("P10").Value = "Yes" Then
        Worksheets(Range("O10").Value).Visible = True
    Else
        Worksheets(Range("O10").Value).Visible = False
    End If
    If Range("P11").Value = "Yes" Then
        Worksheets(Range("O11").Value).Visible = True
    Else
        Worksheets(Range("O11").Value).Visible = False
    End If
    If Range("P12").Value = "Yes" Then
        Worksheets(Range("O12").Value).Visible = True
    Else
        Worksheets(Range("O12").Value).Visible = False
    End If
    If Range("P13").Value = "Yes" Then
        Worksheets(Range("O13").Value).Visible = True
    Else
        Worksheets(Range("O13").Value).Visible = False
    End If
        If Range("P14").Value = "Yes" Then
        Worksheets(Range("O14").Value).Visible = True
    Else
        Worksheets(Range("O14").Value).Visible = False
    End If
   
    If Range("P15").Value = "Yes" Then
        Worksheets(Range("O15").Value).Visible = True
    Else
        Worksheets(Range("O15").Value).Visible = False
    End If   
    If Range("P16").Value = "Yes" Then
        Worksheets(Range("O16").Value).Visible = True
    Else
        Worksheets(Range("O16").Value).Visible = False
    End If
    If Range("P17").Value = "Yes" Then
        Worksheets(Range("O17").Value).Visible = True
    Else
        Worksheets(Range("O17").Value).Visible = False
    End If
 
If Range("J5").Value = "n/a" Then
                  Sheet3.Range("12:12,34:38,41:41,44:44,47:47,52:52,55:55").EntireRow.Hidden = True
    Else
                   Sheet3.Range("12:12,34:38,41:41,44:44,47:47,52:52,55:55").EntireRow.Hidden = False
    End If
    If Sheet3.Range("A48").Value = "Hide" Then
                  Sheet3.Rows("48").EntireRow.Hidden = True
    Else
                   Sheet3.Rows("48").EntireRow.Hidden = False
    End If
    If Sheet3.Range("A49").Value = "Hide" Then
                   Sheet3.Rows("49").EntireRow.Hidden = True
    Else
                  Sheet3.Rows("49").EntireRow.Hidden = False
    End If
    If Range("J5").Value = "n/a" Then
                   Sheet21.Range("13:13,35:39,42:42,45:45,48:48,50:50,53:53,56:56").EntireRow.Hidden = True
    Else
    Sheet21.Range("13:13,35:39,42:42,45:45,48:48,50:50,53:53,56:56").EntireRow.Hidden = False
    End If
   
If Range("a28").Value = "Hide" Then
        Rows("28").EntireRow.Hidden = True
    Else
        Rows("28").EntireRow.Hidden = False
    End If
    If Range("a29").Value = "Hide" Then
        Rows("29").EntireRow.Hidden = True
    Else
        Rows("29").EntireRow.Hidden = False
    End If
   
    If Range("a30").Value = "Hide" Then
         Rows("30").EntireRow.Hidden = True
    Else
         Rows("30").EntireRow.Hidden = False
    End If
   
    If Range("a31").Value = "Hide" Then
         Rows("31").EntireRow.Hidden = True
    Else
         Rows("31").EntireRow.Hidden = False
    End If
   
    If Range("a33").Value = "Hide" Then
         Rows("33").EntireRow.Hidden = True
    Else
         Rows("33").EntireRow.Hidden = False
    End If
   
    If Range("a35").Value = "Hide" Then
         Rows("35").EntireRow.Hidden = True
    Else
         Rows("35").EntireRow.Hidden = False
    End If
       
    If Range("a36").Value = "Hide" Then
         Rows("36").EntireRow.Hidden = True
    Else
         Rows("36").EntireRow.Hidden = False
    End If
   
    If Range("a37").Value = "Hide" Then
         Rows("37").EntireRow.Hidden = True
    Else
         Rows("37").EntireRow.Hidden = False
    End If
       
    If Range("a38").Value = "Hide" Then
         Rows("38").EntireRow.Hidden = True
    Else
         Rows("38").EntireRow.Hidden = False
    End If
   
    If Range("a39").Value = "Hide" Then
         Rows("39").EntireRow.Hidden = True
    Else
         Rows("39").EntireRow.Hidden = False
    End If
       
    If Range("a40").Value = "Hide" Then
         Rows("40").EntireRow.Hidden = True
    Else
         Rows("40").EntireRow.Hidden = False
    End If
       
    If Range("a41").Value = "Hide" Then
         Rows("41").EntireRow.Hidden = True
    Else
         Rows("41").EntireRow.Hidden = False
    End If
    If Range("a42").Value = "Hide" Then
         Rows("42").EntireRow.Hidden = True
    Else
         Rows("42").EntireRow.Hidden = False
    End If
   
    If Range("J4").Value = "Salaried - Exempt" Then
    Sheet10.Columns("I:K").EntireColumn.Hidden = True
    Else
    Sheet10.Columns("I:K").EntireColumn.Hidden = False
    End If
 
    If Range("J4").Value = "Salaried - Exempt" Then
    Sheet11.Columns("I:K").EntireColumn.Hidden = True
    Else
    Sheet11.Columns("I:K").EntireColumn.Hidden = False
    End If
   
    If Range("F15").Value = "No" Then
    Sheet11.Columns("M:S").EntireColumn.Hidden = True
    Else
    Sheet11.Columns("M:S").EntireColumn.Hidden = False
    End If
    If Range("F15").Value = "No" Then
    Sheet14.Rows("14:34").EntireRow.Hidden = True
    Else
    Sheet14.Rows("14:34").EntireRow.Hidden = False
    End If
    If Range("F16").Value = "No" Then
    Sheet14.Rows("33:46").EntireRow.Hidden = True
    Else
    Sheet14.Rows("33:46").EntireRow.Hidden = False
    End If
    If Range("J5").Value = "n/a" Then
    Sheet14.Rows("21:32").EntireRow.Hidden = True
    Else
    Sheet14.Rows("21:32").EntireRow.Hidden = False
    End If
    If Range("J5").Value = "n/a" Then
    Sheet14.Rows("39:40").EntireRow.Hidden = True
    Else
    Sheet14.Rows("39:40").EntireRow.Hidden = False
    End If
     If Range("J5").Value = "n/a" Then
    Sheet14.Rows("44:45").EntireRow.Hidden = True
    Else
    Sheet14.Rows("44:45").EntireRow.Hidden = False
    End If
    If Range("J4").Value = "Salaried - Exempt" Then
    Sheet3.Rows("27").EntireRow.Hidden = True
    Else
     Sheet3.Rows("27").EntireRow.Hidden = False
    End If
        If Range("J4").Value = "Salaried - Exempt" Then
    Sheet3.Rows("48:49").EntireRow.Hidden = True
    Else
     Sheet3.Rows("48:49").EntireRow.Hidden = False
    End If
   
    Application.ScreenUpdating = True
End Sub

**********END CODE******************
 
Last edited by a moderator:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Welcome to MrExcel,

Since you are triggering the code when there's a change in the worksheet, one step to better performance would be to only execute the actions that would be driven by the changed cell.

The parameter Target in the declaration is the cell or cells that were changed. If Target.Address= "$P$2", and the value of $P$2= "Yes" then you only need to execute one corresponding action.
 
Upvote 0
why not put it in the worksheet deactivate event instead of the worksheet change event, then you only do it once when you leave the sheet
 
Upvote 0
Ah - If I select a Target then each time that defined target is updated, it triggers the action. What if the cell in reference "$P$2" is not part of the user's form but a formulated "Yes" after a bunch of selections are made? Can I set the Target.Address to be a reference to a range of cells, each time they are updated to trigger the specified action?
 
Upvote 0
Target is assigned to the cell or cells that were directly modified. If the value of a cell changes because of a dependent formula, that won't be included in Target.

So your event code can check to see if any cells within a range of direct user input cells was changed, then if so, respond to those changes which might include reading updated values of formulas in other cells.
 
Upvote 0
I'm not sure this will speed up your code, but it cleans it up a little. Not knowing the structure of the workbook you are dealing with makes it difficult to offer suggestions, but the on change does trigger whenever there is a change in the workbook, and it cycles through every cell you have referenced in your code. And Modifies every sheet that you have referenced. Maybe it would be faster to start with hiding all the sheets, then only unhide those that need to be unhidden.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim i As Long
    For i = 2 To 17
        If Range("P" & i).Value = "Yes" Then
            Worksheets(Range("O" & i).Value).Visible = True
        Else
            Worksheets(Range("O" & i).Value).Visible = False
        End If
    Next i
 
    If Range("J5").Value = "n/a" Then
        Sheet3.Range("12:12,34:38,41:41,44:44,47:47,52:52,55:55").EntireRow.Hidden = True
        Sheet21.Range("13:13,35:39,42:42,45:45,48:48,50:50,53:53,56:56").EntireRow.Hidden = True
    Else
        Sheet3.Range("12:12,34:38,41:41,44:44,47:47,52:52,55:55").EntireRow.Hidden = False
        Sheet21.Range("13:13,35:39,42:42,45:45,48:48,50:50,53:53,56:56").EntireRow.Hidden = False
    End If
    
    If Sheet3.Range("A48").Value = "Hide" Then
        Sheet3.Rows("48").EntireRow.Hidden = True
    Else
        Sheet3.Rows("48").EntireRow.Hidden = False
    End If
    
    If Sheet3.Range("A49").Value = "Hide" Then
        Sheet3.Rows("49").EntireRow.Hidden = True
    Else
        Sheet3.Rows("49").EntireRow.Hidden = False
    End If
    
    
    For i = 28 To 42
        If Range("a" & i).Value = "Hide" Then
            Rows(i).EntireRow.Hidden = True
        Else
            Rows(i).EntireRow.Hidden = False
        End If
    Next i
   
    If Range("J4").Value = "Salaried - Exempt" Then
        Sheet10.Columns("I:K").EntireColumn.Hidden = True
        Sheet11.Columns("I:K").EntireColumn.Hidden = True
        Sheet3.Rows("27").EntireRow.Hidden = True
        Sheet3.Rows("48:49").EntireRow.Hidden = True
    Else
        Sheet10.Columns("I:K").EntireColumn.Hidden = False
        Sheet11.Columns("I:K").EntireColumn.Hidden = False
        Sheet3.Rows("27").EntireRow.Hidden = False
        Sheet3.Rows("48:49").EntireRow.Hidden = False
    End If
    
    If Range("F15").Value = "No" Then
        Sheet11.Columns("M:S").EntireColumn.Hidden = True
        Sheet14.Rows("14:34").EntireRow.Hidden = True
    Else
        Sheet11.Columns("M:S").EntireColumn.Hidden = False
        Sheet14.Rows("14:34").EntireRow.Hidden = False
    End If
        
    If Range("F16").Value = "No" Then
        Sheet14.Rows("33:46").EntireRow.Hidden = True
    
    Else
        Sheet14.Rows("33:46").EntireRow.Hidden = False
    End If
            
    If Range("J5").Value = "n/a" Then
        Sheet14.Rows("21:32").EntireRow.Hidden = True
        Sheet14.Rows("39:40").EntireRow.Hidden = True
        Sheet14.Rows("44:45").EntireRow.Hidden = True
    Else
        Sheet14.Rows("21:32").EntireRow.Hidden = False
        Sheet14.Rows("39:40").EntireRow.Hidden = False
        Sheet14.Rows("44:45").EntireRow.Hidden = False
    End If
    
    
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
405
Joe4
Joe4
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
J
  • Question Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D
M
  • Question Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K
D
  • Question Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
1K
NoSparks
N

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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