Unhide Row when value is met

[ABabeNChrist]

I’m using this code to hide or unhide rows when a value is met, that part seems to work good, but I not sure where or what is the correct approach to unhide row 6 if the values are met in either A14 or A28, and if values are not met by either one then row 6 will remain hidden.
I’m sure i still need help with my indentions, but I’m working on it.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A14"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A14").Value
      Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
        Sheets("Summary").Rows("7").EntireRow.Hidden = False
        Sheets("Summary").Range("A7").Value = Range("A11").Value & _
        ": " & Range("A14").Value
      Case Else
        Sheets("Summary").Rows("7").EntireRow.Hidden = True
        Sheets("Summary").Range("A7").Value = ""
  End Select
    Application.EnableEvents = True
  End If
   If Not Intersect(Range("A28"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A28").Value
      Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
        Sheets("Summary").Rows("8").EntireRow.Hidden = False
        Sheets("Summary").Range("A8").Value = Range("A25").Value & _
        ": " & Range("A28").Value
      Case Else
        Sheets("Summary").Rows("8").EntireRow.Hidden = True
        Sheets("Summary").Range("A8").Value = ""
  End Select
    Application.EnableEvents = True
  End If
End Sub

[sdckapr]

You could add 2 IFs:
In the Case for rangeA14 you could Have an if for the A28 value which demonstrates both are true and set the row6 hidden attribute as appropriate

And in the Case for rangeA28 you could do an if for the A14 value which demonstrates both are true and set the row6 hidden attribute as appropriate

Steve

[ABabeNChrist]

Hi Steve
I not sure I fully understand how to do this, I tried using this

Code: Select all

If Range("A28").Value = True Then
    Sheets("Summary").Rows("6").EntireRow.Hidden = False
Else
    Sheets("Summary").Rows("6").EntireRow.Hidden = True

[HansV]

Try this (and take a look at the indentation):

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A14"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A14").Value
      Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
        Sheets("Summary").Range("A7").EntireRow.Hidden = False
        Sheets("Summary").Range("A7").Value = Range("A11").Value & _
          ": " & Range("A14").Value
        Sheets("Summary").Range("A6").EntireRow.Hidden = False
      Case Else
        Sheets("Summary").Range("A7").EntireRow.Hidden = True
        Sheets("Summary").Range("A7").Value = ""
        Select Case Range("A28").Value
          Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
            Sheets("Summary").Range("A6").EntireRow.Hidden = False
          Case Else
            Sheets("Summary").Range("A6").EntireRow.Hidden = True
        End Select
    End Select
    Application.EnableEvents = True
  End If
  If Not Intersect(Range("A28"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A28").Value
      Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
        Sheets("Summary").Range("A8").EntireRow.Hidden = False
        Sheets("Summary").Range("A8").Value = Range("A25").Value & _
          ": " & Range("A28").Value
        Sheets("Summary").Range("A6").EntireRow.Hidden = False
      Case Else
        Sheets("Summary").Range("A8").EntireRow.Hidden = True
        Sheets("Summary").Range("A8").Value = ""
        Select Case Range("A14").Value
          Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
            Sheets("Summary").Range("A6").EntireRow.Hidden = False
          Case Else
            Sheets("Summary").Range("A6").EntireRow.Hidden = True
        End Select
    End Select
    Application.EnableEvents = True
  End If
End Sub

[ABabeNChrist]

That works great, thank you HansV
I have another sheet that will be using this same method, but it will have 8 different Case Ranges. How would I add the code that would hide or unhide row 6 if one of the values are met?

[HansV]

It would get very complicated.

[ABabeNChrist]

Hi HansV
Let me explain what I am trying to accomplish. I have a separate sheet within my report workbook that is named “Summary” , this sheet will reflect the areas of concern that were noted on other sheets, that is also within same workbook . So I thought if a certain value is met it would then copy that value and place on the Summary sheet. The reason for hiding and un-hiding row 6 was because that row has the name of that area of concern, Like Bathroom or Bedroom. So that if the values were never met the word Bathroom or Bedroom will never be seen.
Do you have any suggestion?

[HansV]

You could adapt the following code for your purposes:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A14"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A14").Value
      Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
        Sheets("Summary").Range("A7").EntireRow.Hidden = False
        Sheets("Summary").Range("A7").Value = Range("A11").Value & _
          ": " & Range("A14").Value
      Case Else
        Sheets("Summary").Range("A7").EntireRow.Hidden = True
        Sheets("Summary").Range("A7").Value = ""
    End Select
    Application.EnableEvents = True
  End If
  If Not Intersect(Range("A28"), Target) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("A28").Value
      Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
        Sheets("Summary").Range("A8").EntireRow.Hidden = False
        Sheets("Summary").Range("A8").Value = Range("A25").Value & _
          ": " & Range("A28").Value
      Case Else
        Sheets("Summary").Range("A8").EntireRow.Hidden = True
        Sheets("Summary").Range("A8").Value = ""
    End Select
    Application.EnableEvents = True
  End If
  ' Add code for other cells here
  If Not Intersect(Range("A42"), Target) Is Nothing Then
    ' ...
  End If
  ' ...

  ' *** New code ***
  Dim oCell As Range
  If Not Intersect(Range("A14,A28,A42,A56,A70,A84,A98,A122"), Target) Is Nothing Then
    Application.EnableEvents = False
    Sheets("Summary").Range("A6").EntireRow.Hidden = True
    For Each oCell In Range("A14,A28,A42,A56,A70,A84,A98,A122")
      Select Case oCell.Value
        Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs"
          Sheets("Summary").Range("A6").EntireRow.Hidden = False
          Exit For
      End Select
    Next oCell
    Application.EnableEvents = True
  End If
End Sub

Note that the code to hide / unhide row 6 has been moved to the end.

[ABabeNChrist]

Thank you HansV
It work Great
MANY MANY THANKS

Note that the code to hide / unhide row 6 has been moved to the end

Yeah I read something earlier today that mentioned the same thing that
Hidden = True
Will come before
Hidden = False

[HansV]

Tha't not necessarily always true, but it seemed easier to start by hiding row 6, then check each of the conditions, and as soon as one is satisfied, unhide it and stop checking.

[ABabeNChrist]

Very Cool
Thank you