Unhide Row when value is met

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Unhide Row when value is met

Post by 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. :grin:

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

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: Unhide Row when value is met

Post by 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
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Unhide Row when value is met

Post by ABabeNChrist »

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

Code: Select all

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

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Unhide Row when value is met

Post by 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
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Unhide Row when value is met

Post by 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?

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Unhide Row when value is met

Post by HansV »

It would get very complicated.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Unhide Row when value is met

Post by 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?

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Unhide Row when value is met

Post by 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.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Unhide Row when value is met

Post by 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

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Unhide Row when value is met

Post by 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.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Unhide Row when value is met

Post by ABabeNChrist »

Very Cool
Thank you