VBA - Need help running faster/refining code

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:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,003
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
 

MC71517

New Member
Joined
May 10, 2018
Messages
2
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?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

RedWolf3x

New Member
Joined
Apr 11, 2012
Messages
10
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:

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top