VBA Hide Rows Based on Cell Value

[tlc53]

Hi there,

I'm made some progress but not quite there with this code.
It correctly hides the rows if days = 365. However, I would also like it to hide the rows if it is blank (so 365 or Blank).
Also, I have headers on Rows 26:27. I would only like these rows to be visible if any of the results <> = 365 or Blank

Private Sub Worksheet_Change(ByVal Target As Range)
    Rows("28:28").Hidden = Range("I17").Value = 365
    Rows("29:29").Hidden = Range("I18").Value = 365
    Rows("30:30").Hidden = Range("I19").Value = 365
    Rows("31:31").Hidden = Range("I20").Value = 365
    Rows("32:32").Hidden = Range("I21").Value = 365
    Rows("33:33").Hidden = Range("I22").Value = 365
    Rows("34:34").Hidden = Range("I23").Value = 365
    Rows("35:35").Hidden = Range("I24").Value = 365
End Sub

Can someone help me out please? I've already spent waaaayyyyy too much time on this

Thank you!!

 

[dermie_72]

Try this code and see if it works for you. You can change the B Variable if the checker row changes, and the I variable if the start row changes.

Sub tlc53()
Dim B As Long, I As Long
B = 17
For I = 28 To 35
    If Range("I" & B).Value = 365 Or Range("I" & B).Value = "" Then
        Rows(I).Hidden = True
        Else
    End If
    B = B + 1
Next I
End Sub

 

[Peter_SSs]

Give this a try. It deals with hiding & unhiding like your original code as well as the extra requirement re headings.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Long

  For r = 17 To 24
    Rows(r + 11).Hidden = Range("I" & r).Value = 365 Or Len(Range("I" & r).Value) = 0
  Next r
  Rows("26:27").Hidden = Evaluate("COUNTIF(I17:I24,365)+COUNTBLANK(I17:I24)=ROWS(I17:I24)")
End Sub

 

[tlc53]

Give this a try. It deals with hiding & unhiding like your original code as well as the extra requirement re headings.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Long

  For r = 17 To 24
    Rows(r + 11).Hidden = Range("I" & r).Value = 365 Or Len(Range("I" & r).Value) = 0
  Next r
  Rows("26:27").Hidden = Evaluate("COUNTIF(I17:I24,365)+COUNTBLANK(I17:I24)=ROWS(I17:I24)")
End Sub

Works like a charm! Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.