Hide Rows Help

[vonryan]

Dear All,

I am trying to hide rows on certain conditions. These conditions being that is the Date in a particular row is in the past and an adjacent cell has zero value then hide the entire row.

I wrote the macro below which works if a type hide or unhide in Cell AI.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Hide": Rows("5:10").EntireRow.Hidden = True
Case Is = "Unhide": Rows("5:10").EntireRow.Hidden = False
End Select
End If
End Sub

But I have tried to tweak it to basically say

Case if(Range of Cells (B5:B10) is <= TODAY() AND Cells (E5:E10) = 0, EntireRow.Hidden = True

I tried a few variation in syntacs but I do not know the correct way to construct the code.

Please help.

Vonryan

[#2]

change this code
Case if(Range of Cells (B5:B10) is <= TODAY() AND Cells (E5:E10) > 0, EntireRow.Hidden = True

[#3]

Hi @aazirasif 

Thank you for your response. Please advise what to change it too as I do not know.

[#4]

Hi All,

I have manged to solve the mystery myself after a lot of searching.

I eventually learnt how to place buttons on the spreadsheet and trigger VBA functions.

The original code I used was good apart from the OFFSET. I thought OFFSET parameters were Horizontal, Vertical. As it happens it is the other way around.

This is my code now and everything if fine.

Sub Hide()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "I").End(xlUp).Row
On Error Resume Next
For Each c In Range("B5:B348")
If (c.Value < Date And c.Offset(0, 3).Value = 0) Then
c.EntireRow.Hidden = True
ElseIf c.Value >= Date Then
c.EntireRow.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
Sub Unhide()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "I").End(xlUp).Row
On Error Resume Next
For Each c In Range("B5:B348")
c.EntireRow.Hidden = False
Next
On Error GoTo 0
End Sub