Code Clean Up.

[Jason1H]

Good Day Everyone.

I am working on reformatting some of our quality control documents. The current document I am working on has rows hidden to clean up unused data cells. As my crews go through a particular piece of equipment and select what is installed, portions of these rows are unhidden. The code I wrote works but seems a bit cumbersome as I would like to implement this in greater detail for some of our other documents. My primary goal is to keep unused data entry points hidden to minimize the number of sheets required when we go to print off the document. Below is the code, let me know your thoughts and whether or not it can be cleaned up or simplified.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("I16").Value = "Yes" Or Range("Y16").Value = "Yes" Then
Rows("18").EntireRow.Hidden = False
Rows("27").EntireRow.Hidden = False
Rows("34").EntireRow.Hidden = False
Rows("51:52").EntireRow.Hidden = True
ElseIf Range("I16").Value = "" Or Range("I16").Value = "No" And Range("Y16").Value = "" Or Range("Y16").Value = "No" Then
Rows("18").EntireRow.Hidden = True
Rows("27").EntireRow.Hidden = True
Rows("51:52").EntireRow.Hidden = False
End If
If Range("I16").Value = "Yes" Then
Rows("19:22").EntireRow.Hidden = False
ElseIf Range("I16").Value = "" Or Range("I16").Value = "No" Then
Rows("19:22").EntireRow.Hidden = True
End If
If Range("Y16").Value = "Yes" Then
Rows("23:26").EntireRow.Hidden = False
ElseIf Range("Y16").Value = "" Or Range("Y16").Value = "No" Then
Rows("23:26").EntireRow.Hidden = True
End If
End Sub


Please forgive me as I can not remember how to properly insert the code in a thread.

Thanks for your time and comments.

 

[HaHoBe]

Hi Jason,

the event you choose will fire on any movement in the cells of the sheet - no matter if any alteration has been made.

For testing I set up a sheet where the cells I16 and Y16 hold a DataValidation list and only those two cells are monitored with the code. Please test the code on a copy of your workbook, I may have set the wrong rows to hide/unhide:

Private Sub Worksheet_Change(ByVal Target As Range)
' https://www.mrexcel.com/board/threads/code-clean-up.1225743/
' Created: 20221231
' By:      HaHoBe

' Cells I16 and Y16 are changed by user via direct entry or DataValidation (I set uo a DV list for testing)
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("I16, Y16")) Is Nothing Then
  Select Case Target.Address(0, 0)
    Case "I16"
       Select Case Target.Value
        Case "Yes"
          Rows("19:22").EntireRow.Hidden = False
          Rows("18").EntireRow.Hidden = False
          Rows("27").EntireRow.Hidden = False
          Rows("34").EntireRow.Hidden = False
          Rows("51:52").EntireRow.Hidden = True
        Case "", "No"
          Rows("19:22").EntireRow.Hidden = True
          Rows("18").EntireRow.Hidden = True
          Rows("27").EntireRow.Hidden = True
          Rows("34").EntireRow.Hidden = True      '/// added
          Rows("51:52").EntireRow.Hidden = False
      End Select
    Case "Y16"
      Select Case Target.Value
        Case "Yes"
          Rows("23:26").EntireRow.Hidden = False
          Rows("18").EntireRow.Hidden = False
          Rows("27").EntireRow.Hidden = False
          Rows("34").EntireRow.Hidden = False
          Rows("51:52").EntireRow.Hidden = True
        Case "", "No"
          Rows("23:26").EntireRow.Hidden = True
          Rows("18").EntireRow.Hidden = True
          Rows("27").EntireRow.Hidden = True
          Rows("34").EntireRow.Hidden = True      '/// added
          Rows("51:52").EntireRow.Hidden = False
     End Select
    Case Else
      MsgBox "This should not have happened!", vbExclamation, "Wrong cell address"
  End Select
End If
End Sub

Please forgive me as I can not remember how to properly insert the code in a thread.

This link should help remembering.

Ciao,
Holger