Forum Discussion
Hide Rows Help
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
3 Replies
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- change this code
Case if(Range of Cells (B5:B10) is <= TODAY() AND Cells (E5:E10) > 0, EntireRow.Hidden = True
